2
$\begingroup$

I've been toying around with mortgage/loan calculations and spreadsheeting. The loan formula is as follows:

A = p * r *(1+r)^n / ((1+r)^n - 1)

A: payment per period
p: principal
r: interest rate
n: number of periods

This is effectively the PMT function in Excel.

I tried using the Solve function in Mathematica to solve for “r” but it fails. Thought about using a Taylor expansion of the term (1+r)^n but expansions were verrrry big. Any ideas how I can get an approximate solution barring numerical iterations?

----Update to include code

Solve[a == p r (1 + r)^n/((1 + r)^n - 1), r]

Here is the result:
Solve::nsmet: This system cannot be solved with the methods available to Solve.

Thanks

$\endgroup$
3
  • $\begingroup$ Please include Mathematica code that you have tried so far. $\endgroup$
    – Domen
    Mar 9, 2023 at 15:02
  • $\begingroup$ The aim you have is not quite clear. There is no solution to this equation in the case of an arbitrary integer n. You can try to make it for various values of n. In this case, for n>4 you get the solutions in the form of the Root function, which in principle is OK, but as I do not completely understand your aim, I cannot say, if such a solution will fit. Another possibility would be if you know the numbers for all parameters except r, you can easily solve it numerically. It is also possible,if you know only n, but want to study it as the function of A and p. $\endgroup$ Mar 9, 2023 at 15:53
  • $\begingroup$ question updated above. thoughts? $\endgroup$ Mar 9, 2023 at 16:49

2 Answers 2

2
$\begingroup$

If you just want a numerical answer for $r$ given some values of $A$, $p$, and $n$, then FindRoot will do what you want. Example:

With[{a = 20, p = 100, n = 6}, FindRoot[a == p r (1 + r)^n/((1 + r)^n - 1), {r, 1}]] 
(* {r -> 0.0547179} *)

Alternately, if $n$ is a given integer then Mathematica can provide the answer in terms of Root objects. These will abstractly represent the roots of a particular polynomial of order $n$. (Note that roots of polynomials with $n \geq 5$ cannot, in general, be written in terms of roots and elementary math operations, which is why Mathematica does it this way.) One of these will be a reasonable answer, and the others can be discarded as unrealistic.

soln = With[{n = 6}, Solve[a == p r (1 + r)^n/((1 + r)^n - 1), r]]
(* a long and frankly unenlightening output containing six Root objects *)
soln /. {a -> 20, p -> 100}

enter image description here

$\endgroup$
1
  • $\begingroup$ Actually was hoping to solve the equation analytically so I can translate the answer into an excel formula. n can go up to 360 (ie 30 yrs), so yeah much higher than 5 $\endgroup$ Mar 10, 2023 at 0:36
1
$\begingroup$

It is not clear whether a series approximation to be used in Excel or a Mathematica solution is desired.

  1. How do I calculate mortgages and loans using annuities?.
payment = 1725.; 
periods = 30; 
intervals = 1/12; 
loanAmount = 400000.; 
FindRoot[TimeValue[Annuity[payment, periods, intervals], interest, 0] ==
    loanAmount, {interest, 0.04}, AccuracyGoal -> 3]  
  1. Using Mathematica from Excel (and use the above).
    How do I get started using Mathematica from Excel?

  2. How to solve your apparent issue directly in Excel. It is builtin and has been for about decades.
    Excel RATE function: formula examples to calculate interest rate

  3. After working for almost two full days, I was able to generate a reasonably accurate closed form formula. Unfortunately, it is not small. It has almost one thousand terms!

I defined a new value, interestPaid, to handle a wider range of input values. This value can be computed from the problem's input parameters

interestPaid = (payment/loanAmount)*years*paymentsPerYear - 1.

Next, I wrote a Mathematica function with the desired parameters of the function that I wsa trying to develop.

rate = Function[{interestPaid, years}, Module[{interest}, 
     interest /. FindRoot[TimeValue[Annuity @@ 
          Evaluate[{(interestPaid + 1)/(years*paymentsPerYear), 
            years, 1/paymentsPerYear}], interest/100., 0] == 
        1., {interest, 4.}, AccuracyGoal -> 5]]]; 
rate[interestPaid, years]

Then I computed a table of answers using that function. I warn you that a few error messages are thrown. They do not seem to affect the final result.

Quiet[table = Flatten[ParallelTable[{iP, yr, rate[iP, yr]}, 
      {iP, 0.02, 1, 0.02}, {yr, 1, 30, 1/2}], 1]; ]

The table is actually quite smooth: ListPlot3D[table, PlotRange -> Full] .

Now, let the fun begin:

polys = List @@ Expand[Plus @@ (iP^#1 & ) /@ Range[0, 30]*
      Plus @@ (yr^#1 & ) /@ Range[0, 30]]; 
      
parms = (Symbol[StringJoin["x", ToString[NumberForm[#1, {2, 0}, 
         NumberPadding -> {"0", ""}]]]] & ) /@ 
    Range[0, Length[polys] - 1]; 
    
expr = parms . polys; 

r = Function[{iP, yr}, Evaluate[HornerForm[
      expr /. FindFit[table, expr, parms, {iP, yr}], 
      {iP, yr}]]]; 

This gives a result accurate to four places: $r[.5525, 30]$ gives $3.22412$.

Of course, I may have missed something.

$\endgroup$
2
  • $\begingroup$ Wow… I guess what I was really trying to do was get a closed form mathematical equation to numerically approximate the RATE function in excel. It sounds like you matched the parameters of a very long polynomial. I was thinking maybe do a couple of iterations of newton raphson in the equation or use a taylor series expansion (the latter didn’t work). Thanks for the insights. $\endgroup$ Mar 22, 2023 at 20:30
  • $\begingroup$ Microsoft's Excel Rate function says ''Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value." $\endgroup$
    – anon
    Mar 22, 2023 at 23:13

Your Answer

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

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