Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
Hello,
I'm trying to create a retirement planning spreadsheet but I need help with one of the calculations please. Here is an example: Bob is currently 36 years old and wants to retire at 65. He expects to live to 95 and would like to know based on his current savings how much the shortfall is for his goals and what he needs to save from now until retirement. He would like to have $35,000 (today's dollars) per year for retirement.. (This is not a school problem, I'm creating this problem because it is easier than trying to explain what I want alone) I have figured out the following: Years until retirement: 29 Years income needed: 30 $35,000 today inflates to $62,154.56 in 29 years. Total cost of retirement: This is where I'm stuck. I'm trying to figure out what the total cost of retirement will be while inflating the income needed by 2% per year and investing the captial at a rate of 5.5% per year. I can't just subtract the inflation can I? I don't think the numbers will work correctly. Is there a way to do this WITHOUT populating a table. I would like to just use a formula to do it. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
wrote:
Bob is currently 36 years old and wants to retire at 65. He expects to live to 95 and would like to know based on his current savings how much the shortfall is for his goals and what he needs to save from now until retirement. He would like to have $35,000 (today's dollars) per year for retirement. [....] I have figured out the following: Years until retirement: 29 Years income needed: 30 [....] I'm trying to figure out what the total cost of retirement will be while inflating the income needed by 2% per year and investing the captial at a rate of 5.5% per year. Errata: "years income needed" is 66: 95 - 29. The "total cost of retirement" is not the same as how much "to save from now until retirement". And I do not believe that knowledge of the first leads to the second directly. The "total cost of retirement" is: =FV(2%,66,-FV(2%,29,0,-35000)) where FV(2%,29,0,-35000) is the withdrawal (income) in the 30th year: $35,000 inflated 29 years. However, in order to determine how much "to save from now until retirement", I believe we need to know the net present value of the cash flows (income/withdrawals) during retirement. The amount "to save from now until retirement" can be calculated with the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =PMT(5.5%,29,0,-NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%),1) where NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%) is the net present value at the end of the 29th year of the withdrawals in year 30 through 95. It is the NPV expression that must be array-entered. If you calculate that formula separately in A6 for example, the PMT formula is the following normally-entered formula (just press Enter as usual): =PMT(5.5%,29,0,-A6,1) To parameterize, assume that A1 is the inflation rate (2%), A2 is the investment rate of return (5.5%), A3 is the years until retirement (29), A4 is the life expectancy (95), and A5 is annual withdrawal during retirement in current dollars ($35,000). Then the amount "to save from now until retirement" is the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1) However, INDIRECT is a "volatile" function. That means the formula is recalculated every time any cell in any worksheet in the workbook is edited. That might be okay if you do not have too many such formulas. Alternatively, to avoid "volatile" recalculations, use the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)),0,-A5))*(1+A2),1) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
PS.... I wrote:
The amount "to save from now until retirement" can be calculated with the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =PMT(5.5%,29,0,-NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%),1) [....] =PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1) [....] =PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)),0,-A5))*(1+A2),1) I think the use of NPV and FV makes the formula's intent clear. But generally, I avoid array-entered formulas because they are error-prone: we forget ctrl+shift and just press Enter unconsciously. You might consider the following normally-entered alternatives (just press Enter as usual): =PMT(5.5%,29,0,-35000*SUMPRODUCT((1+2%)^ROW($29:$94) /(1+5.5%)^(ROW($29:$94)-29)),1) =PMT(A2,A3,0,-A5*SUMPRODUCT((1+A1)^ROW(INDIRECT(A3&":"&A4-1)) /(1+A2)^(ROW(INDIRECT(A3&":"&A4-1))-A3)),1) =PMT(A2,A3,0,-A5*SUMPRODUCT((1+A1)^ROW(INDEX($A:$A,A3):INDEX($A: $A,A4-1)) /(1+A2)^(ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1))-A3)),1) If you understand the mathematical formulas for NPV and FV, the SUMPRODUCT expression might be clear. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
[Apparently eternal-september.org is having some problems. Eventually,
the following might appear to be a duplicate posting. Sorry about that.] Subject: Financial Time Value of Money calculations Date: Sat, 30 Mar 2013 14:18:39 -0700 Message-ID: PS.... I wrote: The amount "to save from now until retirement" can be calculated with the following array-entered formula (press ctrl+shift+Enter instead of just Enter): =PMT(5.5%,29,0,-NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%),1) [....] =PMT(A2,A3,0, -NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1) [....] =PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)), 0,-A5))*(1+A2),1) I think the use of NPV and FV makes the formula's intent clear. But generally, I avoid array-entered formulas because they are error-prone: we forget ctrl+shift and just press Enter unconsciously. You might consider the following normally-entered alternatives (just press Enter as usual): =PMT(5.5%,29,0,-35000*SUMPRODUCT((1+2%)^ROW($29:$94) /(1+5.5%)^(ROW($29:$94)-29)),1) =PMT(A2,A3,0,-A5*SUMPRODUCT((1+A1)^ROW(INDIRECT(A3&":"&A4-1)) /(1+A2)^(ROW(INDIRECT(A3&":"&A4-1))-A3)),1) =PMT(A2,A3,0,-A5 *SUMPRODUCT((1+A1)^ROW(INDEX($A:$A,A3):INDEX($A:$A ,A4-1)) /(1+A2)^(ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1))-A3)),1) If you understand the mathematical formulas for NPV and FV, the SUMPRODUCT expression might be clear. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
On Thursday, March 28, 2013 10:20:24 AM UTC-5, Jim Thornton wrote:
Hello, I'm trying to create a retirement planning spreadsheet but I need help with one of the calculations please. Here is an example: Bob is currently 36 years old and wants to retire at 65. He expects to live to 95 and would like to know based on his current savings how much the shortfall is for his goals and what he needs to save from now until retirement. He would like to have $35,000 (today's dollars) per year for retirement. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Time Value of Money calculations
Just to make a correction in the last two function calls when interest is to be compounded monthly
The present value is found as it was listed in my last reply =tadPV ( 5.5%, 2%, 0%, 30, -62154.56, 0, 1, 0, 1/12 ) But the payment will now take this new present value as the sinking fund so I will put in XXXXX as a replacement for the future value in the tadPMT formula =tadPMT ( 5.5%, 2%, 0%, 29, 0, XXXXX , 1, 0, 1/12 ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TIME X MONEY | Excel Worksheet Functions | |||
change time into money | Excel Discussion (Misc queries) | |||
Time Value of Money | Excel Worksheet Functions | |||
ROI Calculations for Hard Money Lenders (HML) | Excel Discussion (Misc queries) | |||
Time Value of Money | Excel Worksheet Functions |