Closed Thread
Results 1 to 6 of 6

Excel 2007 : Net Present Value Calculation with 2 different Payments

  1. #1
    Registered User
    Join Date
    11-01-2009
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation Net Present Value Calculation with 2 different Payments

    Hello. I am struggling with calculating the net present value of payments with a 12% discount rate. The first 3 years (total of 36 payments) the payment is $950, and the subsequent 3 years (total of 36 payments) the payment is $450. The payments are made at the beginning of the year. I know from the book I am using the answer for this is $38,453 (they have it broken out that the first 36 payments NPV is $28,888 and the second 36 payments are $9,565). However, I am struggling how to do this within excel.

    First, I determined I should use the PV function, as I needed to show there are payments at the beginning of the term vs. the end. Using the formula:
    =PV(12%/12,36,950,1) I got an answer of $28,602.83, which is pretty close to the $28,888. However, I can't use this formula for the next 3 payments, as they are actually the 4th, 5th, and 6th payments.

    I also tried doing a 'blended' rate of $700 (average of the 950 and 450 pmts over 6 years), =PV(12%/12,72,700,1) ,and came up with $35,805.76, but this is too far away from the $38,453, so I can't use this as a long-term solution.

    Can anyone help? I've been searching the internet exhaustively and can't find a solution!

    Thanks!

    Ursula

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Net Present Value Calculation with 2 different Payments

    Hi

    With 950 in A1:A35, and 450 in B1:B35, use the following.

    Please Login or Register  to view this content.
    The NPV formula assumes a cashflow at the beginning of the period, (unlike the PV where you can specify the start or end), hence you need to add the first value of 950 to the NPV for the subsequent 35 periods.

    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-01-2009
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Net Present Value Calculation with 2 different Payments

    Thank you very much!

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Net Present Value Calculation with 2 different Payments

    Thanks for the feedback.

    Would you mark the original post as 'Solved' - see the 'How To..?' drop down on the blue header bar, and if appropriate rate the response.

    Regards

  5. #5
    Registered User
    Join Date
    06-30-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Net Present Value Calculation with 2 different Payments

    The NPV formula assumes a cashflow at the beginning of the period, (unlike the PV where you can specify the start or end), hence you need to add the first value of 950 to the NPV for the subsequent 35 periods.

    As per your statement above, you mean, by adding the first installment to the NPV of 35 installments gives you the NPV of the Payment at End of the Period,

    If am wrong please correct me...

    regards
    sing_ideas

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Net Present Value Calculation with 2 different Payments

    Sing_ideas,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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