## Introduction

I know, this is probably *not* very exciting stuff, but may turn useful
for some of you one day… Maybe for calculating your car / house repayments, or
maybe writing a program that needs to calculate this.

The basis for this set of function is derived from the Microsoft Excel
formula documentation on the subject and with lots of help of our own in house
“Math teacher” (thanks Andrew) we managed to put this together for the
benefit of the community and as our humble contribution to the forum.

Firstly – here is the Excel formula in its raw format

Now to some relevant terminology…

A “loan” is made up of several elements, each playing a different role in
the equation. At times, you may need to know different things about the loan;
therefore I provided here several functions.

**NPV** | or Net Present Value, this is the amount of the loan |

**FV** | or Future Value (or sometime known as Residual, or ‘Pay-Out’) is
the amount of money that will still be outstanding as the final payment
AFTER all the payments are made. |

**NumPay** | Number of Payments / Installments, for example if a monthly payment is
made for a period of 5 years, this will equal 60. |

**IntRate** | Interest Rate, expressed as say 10.00 (for 10%) |

**bStart** | this should be a 0 or 1, if 0, each repayment is made at the end of
the period (end of month) otherwise, payments are made ahead of each
period. |

OK – enough of that blurb… here are the actual functions:

double MKCalcPayment(int NumPay, double IntRate, double NPV, double FV,
BOOL bStart)
{
IntRate /= 1200.00;
double P = (- NPV * pow(1+IntRate,NumPay) + FV) /
((1 + IntRate * bStart)*((pow((1 + IntRate),NumPay) - 1) /
IntRate));
return P * (-1);
}
double MKCalcInterest(double NumPay, double Payment, double NPV, double FV,
BOOL bStart)
{
double IntRate = 1.00 / 1200.00;
NPV *= (-1);
double iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) /
(NPV*IntRate + Payment + Payment*IntRate*bStart) ) /
log (1 + IntRate);
if (iPer > NumPay)
{
while (iPer > NumPay)
{
IntRate -= 0.000001;
iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) /
(NPV*IntRate + Payment + Payment*IntRate*bStart) ) /
log (1 + IntRate);
}
}
else
{
while (iPer < NumPay)
{
IntRate += 0.000001;
iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) /
(NPV*IntRate + Payment + Payment*IntRate*bStart) ) /
log (1 + IntRate);
}
}
return IntRate * 1200.00;
}
double MKCalcPeriods(double Payment, double IntRate, double NPV, double FV,
BOOL bStart)
{
IntRate /= 1200.00;
NPV *= (-1);
return log((Payment + Payment*IntRate*bStart - FV*IntRate) /
(NPV*IntRate + Payment + Payment*IntRate*bStart) ) /
log (1 + IntRate);
}
double MKCalcResidual(double Payment, int NumPay, double IntRate, double NPV,
BOOL bStart)
{
IntRate /= 1200.00;
return -Payment * ((1 + IntRate * bStart)*((pow((1 + IntRate),NumPay) - 1)
/ IntRate)) + (NPV * pow(1+IntRate,NumPay));
}

All of the above functions are arithmetic juggling of the original formula,
with the exception of the one named `MKCalcInterest`

which does a bit
of “Guessing” as to what the Interest rate is for a set of given arguments.
If you read the Excel on line help you will see that there is no direct way of
finding the answer otherwise.

## So now – lets look at some examples

- I want a loan of $25,000 for a 5-year period with monthly repayments and
an annual compound interest rate of 10% and ZERO residual at the end.
Calling the
`MKCalcPayment`

function results in $531.18 per
month.
- If on the other end, I can afford to pay only $400.00 and on the same
amount I wish to borrow (at the same interest rate) how long will it take me
to repay this? Calling the
`MKCalcPeriods`

shows that we now have
to pay 88.65 payments instead of 60…
- Well now – what if I want to stay with the 60 instalments, but I can
only afford that same $400.00 a month – there will obviously be some left
over (residual) – calling the
** **`MKCalcResidual `

returns
this to be equal to $10,157.89. Well it seems you cant have it all – you
either pay more each month, or you pay for a longer period, or you are left
out with something to pay at the end…
- Finally, what if I want to find out – what will be the actual effective
/ compound annual interest rate on that same amount – if I borrow it for a
60 month period, pay $550.00 per month and call the
`MKCalcInterest`

function – the answer is 11.52% per annum… This means that you should be
paying less than 60 installments – otherwise you are paying too much. See
why by looking at the 1^{st} example.

Well, I am just about done, you can play with more examples – paying ahead
or in arrears, with or without a residual etc..

Have fun