ADVANCED PROBLEMS FOR SPREADSHEET APPLICATION can you show the formula? thanks 1
ID: 2743709 • Letter: A
Question
ADVANCED PROBLEMS FOR SPREADSHEET APPLICATION
can you show the formula? thanks
1. Monthly amortization schedule. Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on the condo and are taking out a loan for the whole amount for the condo for twenty years at 7.0% interest.
a. What is the monthly payment on the mortgage? Construct the amortization of the loan for the twenty years in a spreadsheet to show the interest cost, the principal reduction, and the ending balance each month.
Explanation / Answer
Amount_Financed MB0 $ 6,50,000.00 Rate R 7.00% Monthly rate r=R/12 0.58% Term (years) T' 20 Term (months) T=T'*12 240 (Monthly Payment=MP=MB0*(r)/(1-(1/(1+r)^(T))) $ 5,039.44 MP=650000*(7%/12)/(1-(1/(1+7%/12)^(240)) Simple Amortization Schedule Input Area Amount_Financed 6,50,000.00 Rate 7.00% Term (years) 20.00 Monthly Payment $5,039.44 (=PMT(Rate/12,Term*12,-Amount_Financed)) $5,039.44 (=Amount_Financed*(Rate/12)/(1-(1/(1+Rate/12)^(Term*12)))) Loan Date 15-03-2010 Balance in a month=Balance in previous month-Principal paid in the month Total Principal paid till nth month=Initial Mortgage balance-Mortgage Balance in the month Date Payment Interest Cost Principal Balance Month# (=Monthly Payment) (=Balance in prevoius month*Rate/12) (=Payment-Interest) Totals $12,09,466.34 $5,59,466.34 $6,50,000.00 15-03-2010 6,50,000.00 0 15-04-2010 5,039.44 3,791.67 1,247.78 6,48,752.22 1 15-05-2010 5,039.44 3,784.39 1,255.06 6,47,497.17 2 15-06-2010 5,039.44 3,777.07 1,262.38 6,46,234.79 3 15-07-2010 5,039.44 3,769.70 1,269.74 6,44,965.05 4 15-08-2010 5,039.44 3,762.30 1,277.15 6,43,687.91 5 15-05-2014 5039.44 3,380.19 1659.250476 577802.3391 50 15-08-2019 5,039.44 2,645.84 2,393.60 4,51,179.60 113 15-10-2019 5,039.44 2,617.84 2,421.61 4,46,350.44 115 15-11-2019 5,039.44 2,603.71 2,435.73 4,43,914.70 116 15-12-2019 5,039.44 2,589.50 2,449.94 4,41,464.76 117 15-11-2029 5,039.44 144.45 4,895.00 19,867.20 236 15-12-2029 5,039.44 115.89 4,923.55 14,943.65 237 15-01-2030 5,039.44 87.17 4,952.27 9,991.38 238 15-02-2030 5,039.44 58.28 4,981.16 5,010.22 239 15-03-2030 5,039.44 29.23 5,010.22 -0.00 240
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.