Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    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 built-in function to do this, what's the formula for it (I'm not a mathematician and it looks hard!)?
    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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(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. #3
    2 Star Lounger
    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 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. #4
    2 Star Lounger
    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 got-at.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 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. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 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 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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    2 Star Lounger
    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 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. #8
    WS Lounge VIP sdckapr's Avatar
    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 16-yr old daughter insists: "Dad, you are a GEEK!"
    Steve

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 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.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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