1

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

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!

  • Did you try the code in the answer you mentioned in your question? – mhoran_psprep Feb 10 '15 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. – Przemyslaw Remin Feb 10 '15 at 15:02
2

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))

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

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? – Przemyslaw Remin Feb 12 '15 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. – Chris Degnen Feb 12 '15 at 13:48
  • Hi Chris, do I have to set compounding here or compounding is meaningless for calculation of APR in this case? – Przemyslaw Remin Feb 12 '15 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. – Chris Degnen Feb 20 '15 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)) – Chris Degnen Feb 20 '15 at 13:56
3

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 Feb 10 '15 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 Feb 10 '15 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? – Przemyslaw Remin Feb 11 '15 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. – JoeTaxpayer Feb 11 '15 at 13:56
  • VBA more accurately is 'Visual Basic for Applications', aka the Excel (and other Office applications) macro programming language. – Joe Feb 11 '15 at 17:09
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.

  • 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. – JoeTaxpayer Feb 10 '15 at 16:32
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.