6
$\begingroup$

Be patient with me since this is my first post here. I don't know how to use the Math notation.

I wish to compute the interest rate for an existing loan given the original loan amount, length of loan, and (fixed) monthly payment.

I though of doing that by writing a formula to calculate the interest rate payed for a loan using Mathematica and then using the Solve function.

This first part of the formula tells me how much I will owe to bank after than period of days.

As a formula: $$LoanAmount*(1 + \frac{InterestRate}{100})^{\frac{NumberOfDays}{365}}$$.

As Mathematica is:

LoanAmount*(1 + InterestRate/100)^{NumberOfDays/365)

After the first month the formula become:

$$LoanAmount*(1 + \frac{InterestRate}{100})^{\frac{1}{12}} - MonthlyPayment$$

After the second month: $(LoanAmount*(1 + \frac{InterestRate}{100})^{\frac{1}{12}} - MonthlyPayment)*(1 + \frac{InterestRate}{100})^{\frac{1}{12}} - MonthlyPayment$

Something similar.. but I'm getting mad trying to figure it out :-(

How can I set this kind of calculation?

$\endgroup$
6
  • $\begingroup$ I've improved the Mathjax and kept away the payments. I would like some help to solve a very generic problem given the basic formula. I will also reformulate the problem in a more generic way. $\endgroup$
    – Revious
    Oct 2, 2014 at 15:53
  • $\begingroup$ @YvesKlett: the problem at the moment is still creating the recursive formula which should be a \sum.. but I don't know how exactly.. $\endgroup$
    – Revious
    Oct 2, 2014 at 16:13
  • 1
    $\begingroup$ I have trouble understanding what precisely you are asking. I think you wish to compute the interest rate for an existing loan given the original loan amount, length of loan, and (fixed) monthly payment. Is that correct? What is "InterestTax" and what does it have to do with this calculation? $\endgroup$
    – Mr.Wizard
    Oct 2, 2014 at 18:08
  • 2
    $\begingroup$ I believe that the third example of the Generalizations and Extensions section of the Anuity doc page should provide you with all the information you need. $\endgroup$ Oct 2, 2014 at 18:22
  • $\begingroup$ @Mr.Wizard: sorry, for InterestTax I meant InterestRate. You understood exactly what I want to do! I was trying to write the math formula and then use the Solve[..., InterestRate] to get the solution. Question edited. $\endgroup$
    – Revious
    Oct 2, 2014 at 20:06

2 Answers 2

4
$\begingroup$

Let's assume you take a credit of 10000 with monthly repayments of 100. Interest of 7% p.a. is computed monthly on the remaining balance:

Rest @ NestList[# + #*0.07/12 - 100 &, 10000, 13]

{9958.33, 9916.42, 9874.27, 9831.87, 9789.22, 9746.33, 9703.18, 9659.78, 9616.13, 9572.22, 9528.06, 9483.64, 9438.96}

The above figures are the month end balances. After 12 months you still owe 9438.96

ListLinePlot[
 Rest @ NestList[# + #*0.07/12 - 100 &, 10000, 12*13],
 GridLines -> Automatic]

enter image description here

The loan will be repaid in approx. 150 months.

$\endgroup$
3
  • $\begingroup$ Thanks, I didn't know NestList but I have a doubt. I've always thought that the formula should be #*1.07^(1/12) - 100 (I think interest rate are multiplicative). Is it possible to compare the results of the two formulas with the other function advised by SjoerdC.deVries (Annuity) to see which is the correct one? $\endgroup$
    – Revious
    Oct 2, 2014 at 20:23
  • 1
    $\begingroup$ @Revious Good question & comment. However, they are very difficult to answer. There are 999 ways how banks calculate interest rates. I would like to come back to your question tomorrow :) $\endgroup$
    – eldo
    Oct 2, 2014 at 20:40
  • 2
    $\begingroup$ @Revious You should not be so quick to Accept an answer. Seeing that a Question already has an accepted answer may discourage new ones. Also you indicate uncertainly in this case so it doesn't seem that you are fully satisfied with this answer. $\endgroup$
    – Mr.Wizard
    Oct 2, 2014 at 20:47
3
$\begingroup$

As Sjoerd commented I believe these kinds problems can be solved using TimeValue and Annuity, as detailed in the documentation.

For example if you have a monthly payment of \$304.22 on a 36 month \$10,000 loan you can calculate your interest rate as:

FindRoot[TimeValue[Annuity[304.22, 36], i/12] == 10000, {i, 0.1}]
{i -> 0.0600014}   (* 6% yearly interest *)

If you do not have access to these finance functions or you simply prefer a more transparent approach here is an old function I put together many years ago as an exercise, slightly refined:

f[a_List] := Tr @@ f[a, 1]
f[a_, m_?Negative, t___] := f[a, {m, All}, t]
f[{p_, n_, i_}, {z__, m_} | {m_List} | m_, t_: 0] := 
  Module[{j, y = p ((1 + i)^(# - 1) i)/((1 + i)^n - 1) &},
    Table[y@j, #] &[ {j, z, m} /. {e_?Negative :> n + e + 1, All -> n} ] //
      {(1 - t) (y[n + 1] - #), #}\[Transpose] &
  ]

(Apologies as this clearly wasn't written with readability in mind.)
My notes say I based this on A Derivation of Amortization by Bret D. Whissel.

The one (list) parameter syntax for this function returns the payment:

f[{10000, 36, 0.06/12}]
304.219

We can therefore search for this value using FindRoot, as in the first example:

FindRoot[f[{10000, 36, i/12}] == 304.22, {i, 0.1}]

FindRoot::lstol: The line search decreased the step size to within tolerance specified by AccuracyGoal and PrecisionGoal but was unable to find a sufficient decrease in the merit function. You may need more than MachinePrecision digits of working precision to meet these tolerances. >>

{i -> 0.0600014}

A warning message is issued but the result is still correct.

The two parameter syntax returns a form of amortization table with interest and principal, e.g. first three payments:

f[{10000, 36, 0.06/12}, 3]
{{50., 254.219}, {48.7289, 255.49}, {47.4515, 256.768}}

Fourteenth through seventeenth payments:

f[{10000, 36, 0.06/12}, {14, 17}]
{{32.9708, 271.249}, {31.6146, 272.605}, {30.2515, 273.968}, {28.8817, 275.338}}

Last two payments:

f[{10000, 36, 0.06/12}, -2]
{{3.01953, 301.2}, {1.51353, 302.706}}

All payments, plotted:

f[{10000, 36, 0.06/12}, All] // Accumulate // Transpose // ListLinePlot

enter image description here

$\endgroup$
7
  • 1
    $\begingroup$ That is today the second time that you reopen an interesting question. Carry on. $\endgroup$
    – eldo
    Oct 2, 2014 at 20:54
  • 1
    $\begingroup$ @eldo Thanks for noticing. $\endgroup$
    – Mr.Wizard
    Oct 2, 2014 at 21:01
  • $\begingroup$ Really thanks for the answer, and compliments for the knowledge of mathematica. My skills are really more basics. I will try to play with these function to solve my actual problem. $\endgroup$
    – Revious
    Oct 2, 2014 at 21:19
  • 1
    $\begingroup$ I hope my decision to vote to close does not come across too gung-ho. The first incarnation of the question was rather unclear. Not sure about the best MO. I would guess possible closure/reopening does lead to good results at times rather than leaving unclear Qs open? $\endgroup$
    – Yves Klett
    Oct 3, 2014 at 3:20
  • 1
    $\begingroup$ @Yves I don't know if you are addressing me or someone else, but in case you missed it I also voted to close (which of course as a moderator means an immediate close), pending clarification of the question, so yes I feel that closing an unclear question is the appropriate action. That saves wasted effort on answers, helps the OP get more and more focused answers, and provides the necessary motivation for people to write clear questions. It's "win-win" as they say. $\endgroup$
    – Mr.Wizard
    Oct 3, 2014 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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