Question

Show work through excel using formulas: but please actually show each step you use to do it on excel!

1. Assume you have taken out a balloon mortgage loan for $2,500,000 to finance the purchase of a commercial property. The loan has a term of 5 years, but amortizes over 25 years. Calculate the balloon payment at maturity (Year 5) if the interest rate on this loan is 4.5%.

A. $5,637.99 B. $13, 895.82 C. $2,196,447.59 D. $2,495,479.19

Answer 1

Computation of monthly payment
EMI = [P x R x (1+R)^N]/[(1+R)^N-1]
Where,
EMI= Equal Monthly Payment
P= Loan Amount
R= Interest rate per period
N= Number of periods
= [ $2500000x0.00375 x (1+0.00375)^300]/[(1+0.00375)^300 -1]
= [ $9375( 1.00375 )^300] / [(1.00375 )^300 -1
=$13895.81
Calculation of balloon payment
Present Value Of An Annuity
= C*[1-(1+i)^-n]/i]
Where,
C= Cash Flow per period
i = interest rate per period
n=number of period
= $13895.81[ 1-(1+0.00375)^-240 /0.00375]
= $13895.81[ 1-(1.00375)^-240 /0.00375]
= $13895.81[ (0.5927) ] /0.00375
= $2,196,447.28