Peter
The easiest way is to break the loan into two parts. The portion that gets
paid off and the balloon amount.
The monthly payment is calculated using the PMT function with the amortized
portion of the loan as the principle PLUS the interest on the Balloon
payment. Assuming your balloon payment is 40,000 and the total loan is
100,000, your stated annual interest rate is 6% and the term is 10 years
=PMT(0.06/12,10*12,60000)+(40000*.06/12)
Then use the following formulae to calculate the amortization
Remaining Balance = 100000 for the first period and the Prior Period
Remaining Balance less Principle paid for the remaining periods
Interest = Remaining Balance * Annual Interest Rate/12
Principle = Payment - Interest
HTH
PC
