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:

enter image description here

May I refer to the following Q/A:

How to calculate Annual Percentage Rate


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!

  • Did you try the code in the answer you mentioned in your question? Commented Feb 10, 2015 at 14:11
  • I have written that code:-) It is for all equal periodic payments. Now I want to make a new code for option with the last payment different. Commented Feb 10, 2015 at 15:02

4 Answers 4


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

enter image description here

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

enter image description here

which, by induction, gives the closed-form

enter image description here

and, the same, as copyable text

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


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.


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

enter image description here

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.

  • What is k and how much is it? Compounding? 12? Commented Feb 12, 2015 at 13:36
  • 1
    Hi Przemyslaw - k is the iterator variable for the summation. (See index of summation.) It isn't used in the derived closed-form formula. Commented Feb 12, 2015 at 13:48
  • Hi Chris, do I have to set compounding here or compounding is meaningless for calculation of APR in this case? Commented Feb 12, 2015 at 15:15
  • 1
    Hi Przemyslaw - the closed form is derived from the summation by induction. There is an example here: Induction of closed form of summation where the summation s = Σ r^k for k = a to b is converted to a closed-form formula: s = (r^(1 + b) - r^a)/(r - 1). The summation is the start point because it is the clear expression of the calculation, and the formula is the end point because it is easier to use in calculations and solutions. Commented Feb 20, 2015 at 9:28
  • 1
    The formula with extra brackets: s = ((1+r)^(-t))*(fp+(pp*(((1+r)^t)-((1+r)^(1/n))))/(((1+r)^(1/n))-1)) Commented Feb 20, 2015 at 13:56

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:

enter image description here

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.

  • VBA is Microsoft Visual Basic
    – DJohnM
    Commented Feb 10, 2015 at 14:58
  • 1
    This Excel spreadsheet can be automated. Using "Data - What If Analysis - Goal Seek", you tell Excel to adjust the Interest Rate Cell until the Present Value Cell equals the original loan...
    – DJohnM
    Commented Feb 10, 2015 at 15:03
  • Are you sure that the first periodic payment should be discounted? Are you sure that in A2 should be 1 instead of 0? Commented Feb 11, 2015 at 8:46
  • 2
    If payment one is after a month has passed, yes to both questions. That's how it is for loans I've seen, you get the money, but payment is 30 days later. Commented Feb 11, 2015 at 13:56
  • VBA more accurately is 'Visual Basic for Applications', aka the Excel (and other Office applications) macro programming language.
    – Joe
    Commented Feb 11, 2015 at 17:09

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.

  • 1
    I voted up. The answer is clever, regarding the balloon payment. But if any payment beside the final one were different, we'd have an issue. Commented Feb 10, 2015 at 16:32

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

APR_pp_fp = APR
End Function

For the asked question you have to type:


and it should return 0.314392.

Great thanks to Chris Degnen!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .