Question

Using excel!

Snape buys a home for $450000 and puts $50000 down.  He finances the rest at 5.4% for 30 years.  He begins paying an extra $700 per month after 5 years

How much will he end up saving?

When will it be paid off assuming he purchased the home June 1, 1999?

Answer 1

Hey there !!

Let us first list down all the details which we have been given in the question:

A	Purchase Price of House	4,50,000
B	Down Payment	50,000
C	Loan Taken (A-B)	4,00,000
D	Interest Rate	5.40%
E	Period	30 years

Now let us calculate the Equated monthly installment using Excel.

We will use "pmt formula" in excel.

Equated Monthly Installment

= -pmt (rate, nper, pv, fv, type)

Here, rate = 5.4% / 12 (ie. this is per month rate)

nper = No of installements ie 30 years *12= 360

PV = loan amount

FV, Type = 0

After putting the above values in the formula you will get EMI = $2246.12

Now, Payment made till now = $2246.12 * 12* 5

= $134,767 (Year 1 to 5)

Equated Monthly Installment	2,246.12
Year 1-5 total	1,34,767
Principal Amount repaid (Using CUMPRINC function )	31,297	=CUMPRINC(rate, years, principal, 1, 5,0)
Principal Amount Outstanding	3,68,703	= 400,000 - 31,297
Revised Equated Monthly Installment	? 2,946.12	= 2246.12 + 700
Period by which payment will be made	19.06	Years   {((808603.2- 134767)/2946.12)/12}
it will be paid of by	June, 2023

I hope you have looked at all the workings in the bracket....do let me know if you have any doubts...happy studying....all the best !!