Solve can’t solve this… is there a way to approximate it?

Question

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

Answer 1

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}

Answer 2

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]  

2. Using Mathematica from Excel (and use the above).
   How do I get started using Mathematica from Excel?

3. 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

4. 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.