# Thread: =PMT, but backwards (97 sr2b)

1. ## =PMT, but backwards (97 sr2b)

What function must I use to find the number of monthly repayments, each of value R, to discharge a loan of starting value L, with a monthly interest rate of i, the first repayment to be made one month from the drawdown date of the loan?
If there isn't a built-in function to do this, what's the formula for it (I'm not a mathematician and it looks hard!)?
Thanks!

2. ## Re: =PMT, but backwards (97 sr2b)

If I understand correctly the formula you seek is:

<pre>=(ln(R*(1+i))-ln(R-L*i))/ln(1+i)</pre>

Logic:
Intially you owe L
1st month you owe L(1+i)
2nd month: (L(1+i) - R)(1+i) = L (1+i)^2 - R(1+i)
3rd month: (L (1+i)^2 - R(1+i) - R) (1+i) = L (1+i)^3 - R(1+i)^2 - R(1+i)

etc
nth Month L (1+i)^n - R(1+i)^n-1 - R(1+i)^n-2 - ..... - R(1+i)^2 - R(1+i)

This series can be shown to be:

nth month = (L*i(1+i)^n - R (1+i) [(1+i)^(n-1) -1]) / (1+i)

Since at the nth month you want it be be zero, you can solve:

L*i(1+i)^n = R (1+i) [(1+i)^(n-1) -1]
Which can be rearranged:

L*i /[R*(1+i)] = [(1+i)^(n-1) -1] / [(1+i)^n]

L*i / = [(1+i)^(n-1) -1] /

(1+i)^n = R*(1+i) / (R - L*i)

ln [(1+i)^n] = ln [R*(1+i) / (R - L*i)]
Which can be rearranged to the equation above.

Steve

3. ## Re: =PMT, but backwards (97 sr2b)

Thanks, Steve, have just replied to this but got timed out on a flaky Limey connection.
A chemistry lesson and a maths lesson in the same day is going some! What're you like on 8th-century Serbo-Croat grammar?
I **think** your formula is missing a -1 on the end. Using the Goal seek tool on the case i = 0.01, L = 1000, R = 38 gives the anser nearly 30.7 payments, one fewer than your formula.
Repeating with other start values indicates a consistent discrepancy of one payment too many. If one instance is a fluke, isn't lots the equivalent of what you intellectuals call 'induction' and the rest of us call 'experience'?
Work piece attachement in separate post (trying to beat the timeout.)
Thanks again!

4. ## Re: =PMT, but backwards (97 sr2b)

here's the test piece, somewaht got-at.

5. ## Re: =PMT, but backwards (97 sr2b)

Yes, my mistake.
The number I calculate (if you want to follow the math) is the number of MONTHS so the number of payments is 1 less than that (you have the first month without payment). I guess I didn't read closely enough that you asked for the number of PAYMENTS, not the number of MONTHS.

Not good with grammar questions in English, let alone in Serbo-Croat! Chemistry, math, and science in general are my strengths. I dabble with excel and VBA programming to support analysis in chemistry.Steve

6. ## Re: =PMT, but backwards (97 sr2b)

Hi John,

I think this is what Excel's NPER function is for.

Cheers
PS: From the Excel Help file:

NPER
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax
NPER(rate, pmt, pv, fv, type)

For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.

Rate is the interest rate per period.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

Examples

NPER(12%/12, -100, -1000, 10000, 1) equals 60

NPER(1%, -100, -1000, 10000) equals 60

NPER(1%, -100, 1000) equals 11

7. ## Re: =PMT, but backwards (97 sr2b)

Many thanks, Macropod, it looks like Canberra is quicker than Birmingham or Pittsburgh to figure that one person's loan is another person's investment. (Something to do with a brain-dead Monday morning?)
At least we provoked Steve into revising his algebra.
Reassuringly, on constant inputs, =NPER gives identical results to
=((LN(C11*(1+\$G\$6/100))-LN(C11-B11*\$G\$6/100))/LN(1+\$G\$6/100))-1
and I know which I find easier to type!

8. ## Re: =PMT, but backwards (97 sr2b)

My problem is that I never use the financial functions so I do not know what is available.

There is also something to say for also knowing where the numbers come from and where they are going to make sure all the assumptions are correct.

You could just look at the fact that I like to do algebra, and as my 16-yr old daughter insists: "Dad, you are a GEEK!"
Steve

9. ## Re: =PMT, but backwards (97 sr2b)

Hi Steve,

I wholeheartedly agree with the principle of [quoteknowing where the numbers come from and where they are going to make sure all the assumptions are correct[/quote]

To that end, the full evuivalent to Excel's NPER funtion would be:
=-if(rate=0,(pv+fv)/pmt,(log(1+(pv+pmt*type)/pmt*rate)-log(1+(fv+pmt*type)/pmt*rate))/log(1+rate))
where pv, fv, pmt and type are as per my previous post.
Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•