It is not clear whether a series approximation to be used in Excel or a Mathematica solution is desired.
- 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]
Using Mathematica from Excel (and use the above).
How do I get started using Mathematica from Excel?
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
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.
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$