Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NPV of Cash Flow Payments
I have been trying to find a simple answer to my NPV problem without
creating all the monthly cash flows if I have only the following information. I want to NPV a stream of payments that changes every year. For example this is what I know Months Monthly Pmt Amt 1-12 $3.00 13-24 $3.12 25-36 $3.26 37-48 $3.40 49-60 $3.50 Using a 6% discount rate how do I generate the NPV without creating cells to hold each months payment? Is there a way or am I just wishfully thinking here? Any help would be appreciated. AJ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NPV of Cash Flow Payments
On Sep 27, 9:22 pm, AdmiralAJ wrote:
Months Monthly Pmt Amt 1-12 $3.00 13-24 $3.12 25-36 $3.26 37-48 $3.40 49-60 $3.50 Using a 6% discount rate how do I generate the NPV without creating cells to hold each months payment? In this particular case, where the payment amount changes on a regular basis (every 12 months), the most direct (but least flexible) solution might be the following array formula (commit with ctrl-shift-Enter): =SUM( PV(6%/12,12,-{3;3.12;3.26;3.4;3.5},0,0) / (1+6%/ 12)^(12*(ROW($1:$5)-1)) ) Note that the PV "type" argument is 0, which means that payments are presumed to be at the end of each period. That gives the same result as the Excel NPV() function. But I suspect you want "type" to be 1, which means that payments are at the beginning of each period. Of course, the formula can be modified in many ways in order to make it more general. For example, instead of -{3;...}, you might use - A1:A5, a range that contains the payment amounts. And instead of 12*(row(...)-1), you might use (B1:B5-1), a range that contains the payment number each time the repeated amount changes (i.e. corresponds to A1:A5). Lastly, instead of 6%/12, you might use C1, a cell that contains the monthly rate. Finally, when you wrote "a 6% discount rate", I assumed that is the nominal annual rate; ergo, 6%/12 is the monthly rate. If 6% is the monthly rate, obviously you would use that instead of 6%/12 (in two places). But if 6% is the APY (compounded rate), the monthly rate would be RATE(12,0,-1,1+6%) or (1+6%)^(1/12)-1. Thus, (1+m)^12-1 is 6%, where "m" is the monthly rate. This comment applies to the use of NPV as well. HTH. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NPV of Cash Flow Payments
On Sep 29, 11:12 am, joeu2004 wrote:
On Sep 27, 9:22 pm, AdmiralAJ wrote: Months Monthly Pmt Amt 1-12 $3.00 13-24 $3.12 25-36 $3.26 37-48 $3.40 49-60 $3.50 Using a 6% discount rate how do I generate the NPV without creating cells to hold each months payment? In this particular case, where the payment amount changes on a regular basis (every 12 months), the most direct (but least flexible) solution might be the following array formula (commit with ctrl-shift-Enter): =SUM( PV(6%/12,12,-{3;3.12;3.26;3.4;3.5},0,0) / (1+6%/ 12)^(12*(ROW($1:$5)-1)) ) Note that the PV "type" argument is 0, which means that payments are presumed to be at the end of each period. That gives the same result as the Excel NPV() function. But I suspect you want "type" to be 1, which means that payments are at the beginning of each period. Of course, the formula can be modified in many ways in order to make it more general. For example, instead of -{3;...}, you might use - A1:A5, a range that contains the payment amounts. And instead of 12*(row(...)-1), you might use (B1:B5-1), a range that contains the payment number each time the repeated amount changes (i.e. corresponds to A1:A5). Lastly, instead of 6%/12, you might use C1, a cell that contains the monthly rate. Finally, when you wrote "a 6% discount rate", I assumed that is the nominal annual rate; ergo, 6%/12 is the monthly rate. If 6% is the monthly rate, obviously you would use that instead of 6%/12 (in two places). But if 6% is the APY (compounded rate), the monthly rate would be RATE(12,0,-1,1+6%) or (1+6%)^(1/12)-1. Thus, (1+m)^12-1 is 6%, where "m" is the monthly rate. This comment applies to the use of NPV as well. HTH. Thanks Joe! Sorry for the delayed response but I had gone away for vacation. :) I have one other question. Suppose instead of having the monthly amount I was given the sum of the monthly payments. For example, in year 1 the monthly payment is $3.00, but the sum of the monthly payments in year 1 would be $36. How could I modify your formula to deal with this? Would I divide the range by 12? Thanks again for a great formula! AJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cash Flow | Charts and Charting in Excel | |||
How can I Lookup and sum payments for cash flow forecast? | Excel Worksheet Functions | |||
Cash flow charts | Excel Discussion (Misc queries) | |||
Cash Flow | Excel Discussion (Misc queries) | |||
Cash Flow Set-Up | Excel Worksheet Functions |