+ Reply to Thread
Results 1 to 6 of 6

Reverse IRR Calculation with two payment schedules (Target IRR, need to determine payment)

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Reverse IRR Calculation with two payment schedules (Target IRR, need to determine payment)

    Hello,

    I need to do the reverse of the function =IRR(), which is to spit out the annual cash flow required to meet a target IRR. The initial capital outlay is provided, and the cash flows are 15 annual payments. The first 5 annual payments are fixed and known, what I need to determine is the value of the payments for years 6 through 15 such that I achieve my IRR target (24% in this case).

    I am aware of the PMT() function - (thanks to shg for helping me with this in another forum!), but I'm not sure if I can use it with the two payment schedules.

    For example:
    ($1,000) initial capital outlay
    $300 annual cash flow for years 1 - 5
    What is the annual cash flow required in years 6 - 15 such that there is a 24.0% IRR?

    (the answer is ~$140.2, found with goal seek. Can this be done with formulas?)

    Thanks,

    spaniard25

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

    The payment is the amount that makes the NPV of the whole cash flow = 0 at 24%.

    A
    B
    C
    D
    1
    rr
    24%
    C1: Input
    2
    s
    0.8065
    C2: =1 / (1 + rr)
    3
    m
    6
    C3: Input
    4
    n
    16
    C4: Input
    5
    6
    Per
    Amt
    7
    0
    ($1,000.00)
    ($142.25)
    C7: =NPV(rr, B7:B12)
    8
    1
    $300.00
    $ 140.44
    C8: =-$C$7 * (1 - s) / (s^(m+1) - s^(n+1))
    9
    2
    $300.00
    ($0.00)
    C9: =NPV(rr, B7:B22)
    10
    3
    $300.00
    24.00%
    C10: =IRR(B7:B22)
    11
    4
    $300.00
    12
    5
    $300.00
    13
    6
    $140.44
    B13 and down: =$C$8
    14
    7
    $140.44
    15
    8
    $140.44
    16
    9
    $140.44
    17
    10
    $140.44
    18
    11
    $140.44
    19
    12
    $140.44
    20
    13
    $140.44
    21
    14
    $140.44
    22
    15
    $140.44


    I can't rationalize why n=16 instead of 15 at the moment, other than it gives the right answer.
    Last edited by shg; 04-01-2014 at 01:06 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

    Thanks shg. Over at the MrExcel forums (where you helped me with a simpler version of this problem), user joeu2004 used the FV() function embedded in the PMT() function. Fundamentally the end result is the same, it looks like you used actual financial definitions instead of excel formulas. Link here.

    Either way, thanks a lot. It's a pleasure to receive help from intelligent and friendly folks such as yourself.

    spaniard25

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

    You're welcome. Please observe forum courtesy and post a link when you cross-post on another forum.

  5. #5
    Registered User
    Join Date
    12-12-2010
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

    Quote Originally Posted by shg View Post
    The payment is the amount that makes the NPV of the whole cash flow = 0 at 24%.

    A
    B
    C
    D
    1
    rr
    24%
    C1: Input
    2
    s
    0.8065
    C2: =1 / (1 + rr)
    3
    m
    6
    C3: Input
    4
    n
    16
    C4: Input
    5
    6
    Per
    Amt
    7
    0
    ($1,000.00)
    ($142.25)
    C7: =NPV(rr, B7:B12)
    8
    1
    $300.00
    $ 140.44
    C8: =-$C$7 * (1 - s) / (s^(m+1) - s^(n+1))
    9
    2
    $300.00
    ($0.00)
    C9: =NPV(rr, B7:B22)
    10
    3
    $300.00
    24.00%
    C10: =IRR(B7:B22)
    11
    4
    $300.00
    12
    5
    $300.00
    13
    6
    $140.44
    B13 and down: =$C$8
    14
    7
    $140.44
    15
    8
    $140.44
    16
    9
    $140.44
    17
    10
    $140.44
    18
    11
    $140.44
    19
    12
    $140.44
    20
    13
    $140.44
    21
    14
    $140.44
    22
    15
    $140.44


    I can't rationalize why n=16 instead of 15 at the moment, other than it gives the right answer.
    Hello,
    How can you adjust this to have 20 different paying schedules? One each year?!
    Is that possible?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,091

    Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

    faitas welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 08-24-2013, 11:43 AM
  2. Replies: 3
    Last Post: 07-29-2013, 11:31 AM
  3. Loan/Mortgage Payment with a Skipped Payment
    By mbdoris in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-24-2012, 10:10 PM
  4. Payment of Invoices in comparison with the payment period
    By Renato Silva in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2006, 06:50 PM
  5. how do you get a positive number payment with a mortgage payment .
    By sam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2005, 02:06 AM

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