Algorithm to calculate the Interest on Loan with a Balloon Payment

Question

I am trying to understand the algorithm used by My Calculators.com - Balloon Payment Calculator

If I enter the following details:

Loan amount: 4556.75
Number of months: 5
Interest rate: 5.75%
Balloon payment: 3990.37

And click the 'View Amortization Schedule' button it displays me a breakdown of the payments and the totals. The value I am interested in is the total interest which in this example is 122.62.

What is the formula used to end up with this figure? I have tried using IPMT in Excel but the figures returned do not match.

Answer 1

The website uses the same method as described here.

The value of the loan is equal to the sum of the discounted values of the repayments.

∴ b = ((1 + r) (m + (1 + r)^n (r s - m)))/r

and m = (r ((1 + r)^(1 + n) s - b))/((1 + r) ((1 + r)^n - 1))

where

s = present value of loan
m = periodic repayment
r = periodic rate
b = balloon payment
n = number of periods

Using your figures to calculate the monthly payment.

s = 4556.75
r = 5.75/100/12
b = 3990.37
n = 5

m = (r ((1 + r)^(1 + n) s - b))/((1 + r) ((1 + r)^n - 1)) = 137.78

Note, on the website the a figure of 137.80 is required to arrive at a balloon payment of 3990.37. The interest rounds to 122.62 either way, as shown below.

Using the formula for interest paid to-date derived here.

number of months, x = 6
total interest = ((1 + r)^x - 1) s + (m (1 - (1 + r)^x + r x))/r = 122.62

Website screenshots