how to calculate apr on a mortgage with straight line amortization

[Fredrik_Larsson]

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?

Example:

Borrowing amount 100.000

Nominal rate 5 %

Periods 60 (= 5 years monthly payments)

Establishment Fee 100

Monthly Fee 25

Result for Annuity

Interest 13.227

Fees 1.600

Monthly Payment 1.912

Total 114.827

APR 5,73 %

Result for straight line 

Interest 12.710

Fees 1.600

Monthly Payment month 1   2.109

Total 114.310

APR 5,75 %

[#2]

@Fredrik_LarssonThe APR is the IRR of your cashflows.  So lay out your dates and cashflows and use XIRR.

[#3 · Accepted solution]

@Fredrik_Larsson

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:

D8:    =G5

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)

K4:     =B3

G5:    =SUM(H5:J5)

H5:    =$D$9

I5:      =$D$7

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.

[#4]

Great. Thanks for your input.

Fredrik

@ecovonrein 

[#5]

Wow, fantastic. Exactly what I needed.

This is my first post in this forum and I could never expect to get this kind of help.

Thanks alot,
Fredrik

[#6]

@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.