In: Accounting
Using Excel!
Professor McGonagall buys a home for $129,000 and puts $15000 down. She finances the rest for 30 years at 4.25%
If she refinances after 5 years at 3.9%, how much will she save assuming she keeps paying the original amount?
Cost of home | $129,000 | |||||||||
Down payment | $15,000 | |||||||||
Loan amount | $114,000 | |||||||||
Number of months | 360 | (30*12) | ||||||||
Monthly interest | (4.25/12)% | |||||||||
Monthly payment | $560.81 | (Using PMT function ofexcel with rate=(4.25/12)%, Nper=360, PV=-114000) | ||||||||
Future Value of monthly payment for 5 years | $37,417.54 | (Using FV function ofexcel with rate=(4.25/12)%, Nper=60, Pmt=-560.81) | ||||||||
Future value of loan amount after 5 years | $140,938.42 | (Using FV function ofexcel with rate=(4.25/12)%, Nper=60, PV=-114000) | ||||||||
Loan Balance after 5 years | $103,520.87 | (0938.42-37417.54) | ||||||||
Monthly Interest Rate | (3.9/12)% | |||||||||
Monthly Payments | $560.81 | |||||||||
Amount of payments at 4.25% for balance25 years | $168,243.44 | 560.81*(30-5)Years*12months/year | ||||||||
Number of months tobe paid at 3,9% | 282.3341997 | (Uing NPER function of excel with Rate=(3.9/12)%, Pmt=560.81, PV=-103520.87) | ||||||||
Total Payment required | $158,336.26 | (282.3341997*560.81) | ||||||||
Amount of savings | $9,907.18 | (168243.44-158336.26) |
|