how to calculate apr on a mortgage with straight line amortization

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.