+ Reply to Thread
Results 1 to 2 of 2

PPMT Function with varies interest rate

  1. #1
    ExcelMonkey
    Guest

    PPMT Function with varies interest rate

    Does anyone know if its possible to use an existing function in Excel to
    calculate a periodic PPMT % which adds up to 100% WHEN using varied int
    rates. For example: I have a three period rate using 10%:

    =-PPMT(10%,1,3,1) = 30.2%
    =-PPMT(10%,2,3,1) = 33.2%
    =-PPMT(10%,3,3,1) = 36.6%

    Cleary this addds to 100.0% over the three periods. But when the rate jumps
    1% each period from 10% to 11% to 12 % I get:

    =-PPMT(10%,1,3,1) = 30.2%
    =-PPMT(11%,2,3,1) = 33.2%
    =-PPMT(12%,3,3,1) = 37.2%

    Which addes up to 100.6%. How do you get around this in excel. Is there a
    function that can handle this?

    Thanks


  2. #2
    Duke Carey
    Guest

    RE: PPMT Function with varies interest rate

    When you change rates you have to recalculate the remainder as if it were a
    new loan for whatever the remaining balance is, and for whatever the
    remaining term is. In your example you need to use (put these in A1:A3)

    =-PPMT(10%,1,3,1)
    =-PPMT(11%,1,2,1-A1)
    =-PPMT(12%,1,1,1-SUM(A1:A2))


    "ExcelMonkey" wrote:

    > Does anyone know if its possible to use an existing function in Excel to
    > calculate a periodic PPMT % which adds up to 100% WHEN using varied int
    > rates. For example: I have a three period rate using 10%:
    >
    > =-PPMT(10%,1,3,1) = 30.2%
    > =-PPMT(10%,2,3,1) = 33.2%
    > =-PPMT(10%,3,3,1) = 36.6%
    >
    > Cleary this addds to 100.0% over the three periods. But when the rate jumps
    > 1% each period from 10% to 11% to 12 % I get:
    >
    > =-PPMT(10%,1,3,1) = 30.2%
    > =-PPMT(11%,2,3,1) = 33.2%
    > =-PPMT(12%,3,3,1) = 37.2%
    >
    > Which addes up to 100.6%. How do you get around this in excel. Is there a
    > function that can handle this?
    >
    > Thanks
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1