Financial › PV120 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreePV

PV: how big a mortgage you can afford

Reza and his wife can afford a mortgage installment of Rp4,500,000 a month. The bank offers 7.5% annual interest over 15 years. They want to know the maximum loan amount so they can pick a house that fits.

Task

In B5, calculate the maximum loan with PV from the annual rate in B1, the term in years in B2 and the monthly installment in B3. Convert to monthly units inside the formula and show the result as a positive number.

Cells to fillB5

Required functionsPV

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
PV(rate, nper, pmt) calculates the present value of equal payments, which is the loan those installments can pay off.
Hint 2
Match the units: rate B1/12 and number of periods B2*12.
Hint 3
The installment is money going out, so write it with a minus: =PV(…, …, -B3).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

B5

=PV(B1/12,B2*12,-B3)

Explanation

=PV(B1/12,B2*12,-B3) gives about Rp485 million. In other words, Rp4.5 million a month for 180 months pays off a loan of that size, before any down payment. The installments total 180 × 4.5 million = Rp810 million, so about Rp325 million goes to interest. Many mortgages start with a fixed rate and then float, so rerun the numbers with a higher rate.

Starting worksheet: Sheet1
Row	A	B
1	Annual interest	7.5%
2	Term (years)	15
3	Affordable monthly installment	4,500,000
4
5	Maximum loan
6
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result