Simple Balloon loan template?

[koalamotorsport]

Have spent an hour searching the web for a simple balloon loan template. There are none to be had that work, and it seems 90% of the "template" sites out there are just scam sites to generate ad revenue.

I need to calculate the balloon payment at the end of the term. Every template I can find that mentions "balloon" is either for mortgage, or you need to know the balloon amount.


Loan : $120,000
Interest: 1%
Term: 2 years
Monthly payment: $500
Balloon at end of 2 years: ?

Anyone seen a template that works?

Thanks - Brett

[Kaper]

I don't think you need a template. It is just a standard Excel function.
Assuming your data is in column B in the sequence listed above, use:

Please Login or Register  to view this content.

and the result seems to be quite realistic: $110,307.44 to be payed at the end of the 2yrs period
So having in mind that $12,000.00 was payed in monthly instalments the sum of $2,307.44 is real cost of the loan.
It's slightly less than 1% for 2 years, because a part of debt was payed during the period.

[curiouscat408]

Caveat lector.... The term "balloon payment" is used ambiguously in the industry, I discovered.

The FV function calculates the remaining balance after the last payment. I used to think that is the "balloon payment", in part based on the user manual for the HP 12 (40 years ago!).

That would be FV(1%/12, 2*12, 500, -120000).

But some (most?) people use the term "balloon payment" to refer to the entire final payment. That is the remaining balance plus the last payment.

That would be FV(1%/12, 2*12, 500, -120000) + 500.

And in fact, that is more consistent with a cash flow model for calculating 12*(monthly IRR), which should be 1%.