How to calculate Annual Percentage Rate of a loan with unequal periodic payments?

Question

Let's say we borrow a loan of $1000. We pay back to bank $100 per month but for some stupid reason the last payment is smaller and equals $50. This is a common case in my country. How then the APR should be calculated? Here is the schedule plan:

May I refer to the following Q/A:

How to calculate Annual Percentage Rate

https://stackoverflow.com/questions/28050109/excel-vba-formula-for-apr/28084325#28084325

I want to construct VBA code to calculate it. If the author of accepted answer wishes, he may be included as coauthor of the VBA code.

Edit February 23, 2015
I have answered this question with a VBA code for Excel user defined function. As promised author of accepted answer is co-author of the code. Thanks!

Answer 1

As established in the quoted link How to calculate Annual Percentage Rate the summation for a loan is

where s is the loan principal, n is the number of periods per year, t is the number of years, pp is the periodic payment, and r is the annual APR (as effective annual rate).

Splitting out the final payment and calling it fp looks like this

which, by induction, gives the closed-form

and, the same, as copyable text

s = (1 + r)^-t*(fp + (pp*((1 + r)^t - (1 + r)^(1/n)))/((1 + r)^(1/n) - 1))

With

s = 1000.00
n = 12
t = 1
pp = 100
fp = 50

solving for r yields r = 0.314391 or 31.44 % APR effective rate.

Or, converting to a nominal rate depending on preference

((1 + r)^(1/n) - 1)*n = 0.276511 so 27.65 % nominal APR compounded monthly.

Edit

Alternatively, solving for the periodic rate, p, instead of r. The summation

has the closed-form

s = ((1 + p)^(-n*t)*(fp*p + ((1 + p)^(n*t) - 1 - p)*pp))/p

Solving for p has two solutions: p = -1.67608 or p = 0.0230426

From the positive one p*12 = 27.65 % nominal APR compounded monthly.

Answer 2

You have two choices - Use a spreadsheet's IRR function to calculate the return. There are many tutorials for this on the internet. The one thing to note is you need to enter a first line showing -1000, the negative flow out.

Else, you can do it line by line. You need to use the time value of money to get the present value of each payment at a given rate, e.g. 6%/yr = 1.005 (1/2% per month). You then raise 1.005^(# months) and divide that 100 by this number. The sum of all discounted flows will equal 1000 when the chosen rate is correct.

For the second method, this is my result:

The code I used for the C cells is =B2/(C$1^A2) which takes the payment, whatever is in col B, and discounts it by my rate guess raised to the A power. Note, the first guess was 2.5%, since 1150/1000=1.15 (15% total) but the average payment is only 6 months old, so I double that. A few seconds of adjusting up/down, and the result appears to as many digits as you need.

I don't know what VBA is. I hope this explanation can get you going.

Answer 3

A ready-made solution to this type of problem (an irregular last payment) is an online mortgage calculator that can find the interest rate, and provides for the inclusion of a positive or negative "balloon payment".

A balloon payment in mortgage terms is an additional payment made at the end of the mortgage repayment, in addition to, and at the same time as, the last regular payment.

Balloon payments arise for several reasons. For example, once you use the annuity formula to calculate the exact monthly payment for a particular mortgage, you round this calculated amount up or down by possibly half a cent to an exact number of cents, and pay that amount for the life of the mortgage. This slight, unavoidable error accumulates, with interest, and finally pops out as a slightly different last payment.

In the OP's case, the mortgage calculator should be given a $1000 12 month mortgage with twelve $100 regular payments and a -$50 balloon payment.

Answer 4

Here is Excel VBA user defined function for calculating Annual Percentage Rate (APR) based on the following:
Loan Principal - the amount we borrow from the bank
pp - periodic payment (equal installment)
fp - final payment (balloon payment)
c - Number of compounding periods per year
k - Number of periods to pay the loan

Referring to Chris Degnen explanation t=k/c. Note that APR in the function is a starting guess of APR. It is optional so you do not have to worry about that.

Function APR_pp_fp(LoanPrincipal As Double, pp As Double, fp As Double, c As Integer, k As Integer, Optional APR As Double = 0.01, Optional step As Double = 0.000001) As Double
'Authors Przemyslaw Remin, Chris Degnen
'
'LoanPrincipal - the amount we borrow from bank
'pp - The amount of periodic payment
'fp - The amount of final payment (balloon) payment
'c - Number of compounding periods per year
'k - Number of periods to pay the loan
'APR - Starting Annual Percentage Rate starting from 1%, here it will be used as iterator to find correct Loan Principal
'step - how much we change APR, the smaller the step the more precision we get
'
Dim s1 As Double 'Calculated LoanPrincipal based on guessed APR
Dim s2 As Double 'Calculated LoanPrincipal based on guessed APR but slightly higher
Dim target1 As Double 'the squared difference between s1 and LoanPrincipal
Dim target2 As Double 'the squared difference between s2 and LoanPrincipal

Do Until target1 < target2 'we do the loop until the target falls
    s1 = ((1 + APR) ^ (-(k / c))) * (fp + (pp * (((1 + APR) ^ (k / c)) - ((1 + APR) ^ (1 / c)))) / (((1 + APR) ^ (1 / c)) - 1))
    target1 = (LoanPrincipal - s1) ^ 2
    APR = APR + step
    s2 = ((1 + APR) ^ (-(k / c))) * (fp + (pp * (((1 + APR) ^ (k / c)) - ((1 + APR) ^ (1 / c)))) / (((1 + APR) ^ (1 / c)) - 1))
    target2 = (LoanPrincipal - s2) ^ 2
Loop

APR_pp_fp = APR
End Function

For the asked question you have to type:

=APR_pp_fp(1000,100,50,12,12)

and it should return 0.314392.

Great thanks to Chris Degnen!