Oct 02 2022 12:26 AM
Oct 02 2022 12:26 AM
Hi, excel has great financial functions how to calculate apr when amortization is annuity but i am looking for a solution for straight line amortization.
How can I do a calculator in excel that calculates all the values under straight line in the example below?
Borrowing amount 100.000
Nominal rate 5 %
Periods 60 (= 5 years monthly payments)
Establishment Fee 100
Monthly Fee 25
Result for Annuity
Monthly Payment 1.912
APR 5,73 %
Result for straight line
Monthly Payment month 1 2.109
APR 5,75 %
Oct 02 2022 01:10 PM
@Fredrik_LarssonThe APR is the IRR of your cashflows. So lay out your dates and cashflows and use XIRR.
Oct 02 2022 11:46 PM - edited Oct 02 2022 11:53 PMSolution
I found a lot of misinformation about straight-line amortiztion online. So I'll summarize the correct description that I found (click here), just to be sure we're on the same page.
With a normally amortized loan, the monthly principal and interest payments vary, but the total monthly payment is the same. Most Excel financial functions require equal monthly payments.
With a straight-line amortized loan, the monthly principal payment is the same, but the interest and total payments vary. Consequently, we cannot use most Excel financial functions.
Instead, we must create a monthly amortization schedule and use the calculated monthly payments as cash flows to calculate the IRR, which is the APR.
This is demonstrated below. See the attached Excel file for details.
Formulas for normal amortization:
B8: =ROUNDUP(PMT(B4/12, B5, -B3) + B7, 2)
B10: =(1 + RATE(B5, B8, -(B3-B6)))^12 - 1
B11: =B8*B5 + B6
B12: =B7*B5 + B6
B13: =B11 - B3 - B12
Formulas for straight-line amortization:
D9: =ROUNDUP(D3/D5, 2)
D10: =(1 + IRR(G4:G64))^12 - 1
D11: =SUM(G5:G64) + D6
D12: =D7*D5 + D6
D13: =D11 - D3 - D12
Formulas for amortization schedule:
G4: =-(K4 - D6)
J5: =ROUND(K4*$D$4/12, 2)
K5: =K4 - H5
Your results might vary slightly depending on if, when and how you choose to round calculated amounts.
I recommend using Excel IRR instead of Excel XIRR for a couple reasons. First, XIRR requires payment dates, which you did not provide. And the XIRR result will vary needlessly depending on those dates. Second, it appears that you used Excel RATE to calculate the APR for the normally-amortized loan, and Excel IRR is analogous to Excel RATE.
That said, you should use a method of calculating APR that is consistent with the regulations of your country. For example, in the US, we would use 12*RATE(...) and 12*IRR(...), as described in the "Truth in Lending" regulations.
Oct 02 2022 11:50 PM
Nov 17 2022 03:49 PM - edited Nov 17 2022 04:05 PM
@halleyhalligan wrote: ``Thanks a lot for the explanation!``
You wrote that in "reply" to @Fredrik_Larsson . But I am the one who provided the explanation to him.
@halleyhalligan wrote: ``This is just what I was looking for!``
But is it?! You posted a "reply" in another thead (click here), again addressed to the original poster, but perhaps referring to a solution that I provided (marked "best response").
(Or are you interested in the LAMBDA solution?)
If you still need assistance with a problem:
1. Please start a new discussion. It is "poor form" to piggyback a previous discussion, especially one that has been resolved.
2. Please be sure to reply to the person whose posting you are referring to. Alternatively, in your reply, type "@" (without quotes) and select the name of the person whose posting you are referring to.