Logarithmic Equation Error: =LN((1-((PV*R)/(P*12)))^-1)/LN(1+R)

[dave@helmkin.com]

Hello all, this is my first post on the forum

THANK YOU for your help!

I have been tasked with creating a Debt calculator which takes user inputs (total debt amount, monthly debt payments, and annual interest rate), and outputs the number of years it will take to pay off the debt.

The widespread equation solution seems to be called "Annuity (Present Value) - Solve for n", found here: http://financeformulas.net/Number-of-Periods-of-Annuity-from-Present-Value.html

I have simplified the formula so that it can be entered into excel (note I multiplied Payment by 12 so it's also on a yearly basis):

=LN((1-((PV*R)/(P*12)))^-1)/LN(1+R)

Here's the problem: The formula works perfect for certain PV/R/P combinations, but when the PV gets too high for example, the equation breaks and provided this error (Error Function LN parameter 1 value is -20. It should be greater than 0.).

Can this formula be re-written so as to work with a PV of say, 1million?

Thank you for your help!

 

[tkhunny]

Hello all, this is my first post on the forum

THANK YOU for your help!

I have been tasked with creating a Debt calculator which takes user inputs (total debt amount, monthly debt payments, and annual interest rate), and outputs the number of years it will take to pay off the debt.

The widespread equation solution seems to be called "Annuity (Present Value) - Solve for n", found here: http://financeformulas.net/Number-of-Periods-of-Annuity-from-Present-Value.html

I have simplified the formula so that it can be entered into excel (note I multiplied Payment by 12 so it's also on a yearly basis):

=LN((1-((PV*R)/(P*12)))^-1)/LN(1+R)

Here's the problem: The formula works perfect for certain PV/R/P combinations, but when the PV gets too high for example, the equation breaks and provided this error (Error Function LN parameter 1 value is -20. It should be greater than 0.).

Can this formula be re-written so as to work with a PV of say, 1million?

Thank you for your help!

It's not really clear what exactly is wanted...

There are no restrictions on PV. The "restriction" is in the combination of parameters.

For example, if you are trying to solve PV = 100, Pmt = 10, FV = 150, you will have a problem with a negative interest rate.

In your case, you must have (PV*R)/(12*P) < 1, or you will get this error. If you have (PV*R)/(12P) > 1, you're simply not paying enough. Increase P. The periodic payment (12*P) must be at least as great as the interest accumulated in the period between payments (PV*R). There is a little confusion about non-annual modes in this discussion, but that is the general idea.

Given a fixed mode (time between payments) and a fixed interest rate:
1) If you have a fixed initial amount (PV), you must increase P to fix the problem.
2) If you have a fixed payment (P), you must decrease PV to fix the problem.