Determine the Annual Percentage Rate on a loan with a balloon payment - work shown

[rosefin01]

Hi Guys ,

I am feeling like this:

Here's what I have:


[TABLE="width: 687"]
<tbody>[TR]
[TD]VALUES[/TD]
[TD]LABELS[/TD]
[TD]NOTES/FUNCTIONS[/TD]
[/TR]
[TR]
[TD]500,000.00[/TD]
[TD]Loan Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]Term in Years[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 9.95%[/TD]
[TD]Interest Rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 4,369.39[/TD]
[TD]Monthly Payment[/TD]
[TD]=-PMT(B4/12,B3*12,B2,0)[/TD]
[/TR]
[TR]
[TD] 11,909.56[/TD]
[TD]Finance Charge[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]488,090.44[/TD]
[TD]Amount Financed[/TD]
[TD]=B2-B6[/TD]
[/TR]
[TR]
[TD="align: right"]10.238%[/TD]
[TD]APR for fully amortized loan[/TD]
[TD]=12*RATE(B3*12,-B5,B7,,0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]119[/TD]
[TD]Payments of $4369.39[/TD]
[TD]119 months, then balloon payment due[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]454,932.67[/TD]
[TD]Principal Balance at month 119[/TD]
[TD]=SUM(CUMPRINC(B4/12,B3*12,B2,1,B10,0))+B2[/TD]
[/TR]
[TR]
[TD] 3,772.15[/TD]
[TD]Accrued Interest, month 120[/TD]
[TD]=SUM((B13*B4)/360)*30[/TD]
[/TR]
[TR]
[TD]458,704.82[/TD]
[TD]Balloon Payment (10 year mark)[/TD]
[TD]=B13+B14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.417% [/TD]
[TD]INCORRECT APR Balloon [/TD]
[TD]=12*RATE(A3*12,-A5,A2,A14,1) This is the part I'm struggling with several days without an answer. [/TD]
[/TR]
</tbody>[/TABLE]

Can anybody show me how to calculate the Annual Percentage Rate on a loan with a balloon payment (as shown above).

 

[rosefin01]

top

 

[StephenCrump]

What is the question that you're trying to solve?

And is there an answer provided?

 

[rosefin01]

What is the question that you're trying to solve?

And is there an answer provided?

Hello Stephen,

The question that I am trying to solve is this: what Excel function will determine the Annual Percentage Rate for an amortized, fixed rate loan with a balloon payment?

I have a $500,000 loan amount that is amortized over 30-years. However, at the end of 10-years, the loan becomes due and payable. The APR on this loan differs from the APR on a fully amortized loan because of the balloon payment.

The APR for a fully amortized loan is 10.238%. (I verified that with a national loan originator vendor's software). The APR for a the 30-due-in-10 Balloon Loan is 10.345% (using the same national loan origination vendor's software).

How can I find the Balloon Loan APR with Excel? That is the question!

 

[joeu2004]

The APR for a the 30-due-in-10 Balloon Loan is 10.345% (using the same national loan origination vendor's software). How can I find the Balloon Loan APR with Excel? That is the question!

You have many typos in your original posting, and it is difficult to know how things line up with row numbers. I assume your layout is as follows. I show the corrected formulas.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: right"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"]$500,000.00
[/TD]
[TD]loan amt[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"]30[/TD]
[TD]term (yrs)[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"]9.9500%[/TD]
[TD]annl rate
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"]$4,369.39[/TD]
[TD]pmt/mo[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"]$11,909.56[/TD]
[TD]fin chrg[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"]$488,090.44[/TD]
[TD]fin amt[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]10.2379%
[/TD]
[TD]APR[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]119[/TD]
[TD]#actl pmts[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD="align: right"]$454,932.67[/TD]
[TD]bal at 119[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD="align: right"]$3,772.15[/TD]
[TD]accr int, mo 120[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD="align: right"]$454,335.43[/TD]
[TD]balloon pmt, mo 120[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD="align: right"]10.3452%[/TD]
[TD]APR, mo 120[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD="align: right"]$458,704.82
[/TD]
[TD]total last pmt
[/TD]
[/TR]
</tbody>[/TABLE]

A5:   =-PMT(A4/12,A3*12,A2)
A7:   =A2-A6
A8:   =12*RATE(A3*12,-A5,A7,0)
A13:  =-FV(A4/12,A9,-A5,A2)
A14:  =A13*A4/12
A15:  =-FV(A4/12,A9+1,-A5,A2)
A16:  =12*RATE(A9+1,-A5,A7,-A15)
A17:  =A5+A15

I include A13 and A14, just to show how to calculate them simply. We don't need them.

It is easier to assume that you make a regular payment for month 120, and the balloon payment is the balance (A15).

The 10-year APR is based on 120 months (A9+1), not the full term.

And like the full-term APR, the "pv" is the loan less finance charges (A7), not just the loan amount.

The "fv" is the balloon payment, properly signed (-A15).

Finally, the loan "type" is still 0 (end of period), not 1 (beginning of period).

Aside.... Since these are real-world calculations, we really should round the monthly payment in A5. In this case, it does not substantially alter the 10-year APR; but it does change some values in small ways. I don't know if the online calculator does that, though. So I tried to keep everything as you had it for comparison purposes.

 

[rosefin01]

Wow, thank you very much for your answers here! Very grateful. I do need time to process your calculations, which will be done first thing in the AM. Warmest Regards, Joeu2004.