Results 1 to 9 of 9
Thread: =PMT, but backwards (97 sr2b)

20030922, 08:07 #1
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
=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 builtin function to do this, what's the formula for it (I'm not a mathematician and it looks hard!)?
Thanks!

20030922, 11:22 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: =PMT, but backwards (97 sr2b)
If I understand correctly the formula you seek is:
<pre>=(ln(R*(1+i))ln(RL*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)^n1  R(1+i)^n2  .....  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)^(n1) 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)^(n1) 1]
Which can be rearranged:
L*i /[R*(1+i)] = [(1+i)^(n1) 1] / [(1+i)^n]
L*i / = [(1+i)^(n1) 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

20030922, 14:28 #3
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 8thcentury SerboCroat 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!

20030922, 14:29 #4
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: =PMT, but backwards (97 sr2b)
here's the test piece, somewaht gotat.

20030922, 14:50 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 SerboCroat! Chemistry, math, and science in general are my strengths. I dabble with excel and VBA programming to support analysis in chemistry.Steve

20030923, 02:22 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,236
 Thanks
 2
 Thanked 470 Times in 387 Posts
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 lumpsum 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 11Cheers,
Paul Edstein
[MS MVP  Word]

20030923, 09:07 #7
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 braindead 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(C11B11*$G$6/100))/LN(1+$G$6/100))1
and I know which I find easier to type!

20030923, 10:20 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 16yr old daughter insists: "Dad, you are a GEEK!"
Steve

20030923, 21:56 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,236
 Thanks
 2
 Thanked 470 Times in 387 Posts
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.
CheersCheers,
Paul Edstein
[MS MVP  Word]