Tuesday, May 1, 2012

Future Value Of Savings, Pension Fund, Or Investment


Some bank or financial products such as savings deposit, pension fund, education fund, insurance, mutual fund, buy-pawn gold, etc., implement a mandatory fixed payment every month. The money invested can be withdrawn after a certain period. Those kind of investments will make us discipline for savings each month. It also allows the bank or financial company to better manage the money invested as the payment is predictable, and can be invested in a long term because we or the owners do not suddenly withdraw the money. Suppose the money is invested in agriculture, automotive industry, infrastructure, telecommunication, property, etc., it will need many years for those investments to make profit. That is one reason why some long-term investments offer relatively high interest rate, can be above 10% per year.


But we'll be wondering, after decades will that investment get a result like scenario offered? By using the attached Excel sheet, investment simulation can be calculated. On the sheet there are three calculation formulas to compare: FV function of Excel, table, and financial formula.

For example: a pension fund with payment $ 300 each month. This savings or investment has 30 years (360 months) term, with a fixed interest rate of 5% per year. Inflation rate is considered to be fixed each year and equal to 3.38%.

By entering data in yellow cells in the attached Excel sheet, then we get calculation results as shown below.



Seen there are 3 blue cells with 138,948.3 figures. That is the ultimate value of the investment after 30 years or 360 months.

On the left, there is calculation with the FV (Future Value) function from the Excel menu. Note the monthly payment is written with negative sign, because for the FV formula payments are expenditures so must be written as negative number. This formula will be further described below. At the bottom left there is the profit of investment as much as 30,948.3.

In the center there is a calculation using a table. We can see detailed value of money invested for each month. Shown in the table at the beginning of the investment there is a payment of 300. After one month there is 40 cents gain, and because there is an additional payment so the total funds reached 600.40. And so on up to 30 years. This table can calculate up to 40 years or 480 months. If it is not enough, it can be added by copy-paste.

On the left there is a financial calculation using the Future Value annuity formula.


FV formula
Here is the procedure to put the FV formula by the Excel menu. Select the cell that will contain the formula. In the picture below it is cell D12. Point the mouse (red arrow) to Formula Bar, and press the fx (Insert Function) button.


The next window will appear with Excel formulas as shown below. Select the FV formula, the one with blue background, then press the OK button. If the formula does not appear in ‘Select a function’ window, it can be searched by typing FV in ‘Search for a function’ window, in black background, and press the Go button, then the FV formula will appear in ‘Select a function’ window.


Then the Function Arguments window for FV formula will appear as below. Input interest rate (Rate) as cell D10. In the formula window, investment period (Nper) is investment term multiplied by 12, so it will be calculated per month, and can be seen as cell D5 (Number of period). Monthly payment (Pmt) is cell D3, note figure on cell D3 is written with negative sign because payments are expenditures. Then press Ok and the formula will be written and functioned in cell D12.



You can save $ 300 per month as the example above if you quit smoking, do more physical activity such as not using a vehicle for short disctance transport, just walking or cycling. If you've already used to walking then you can try jogging once a week. You will be more healthy and less sick, more productive, happier and enjoy life, and reduce your medical cost.

Some long-term investment do not have a fixed return rate. The calculation described here is a simulation only with average inflation rate and average investment rate. Past performance of an investment product can not 100% guarantee for performance in the future. So do not put all eggs only in one basket.

No comments:

Post a Comment

Your positive comment will be highly appreciated to improve this site