How to Create Excel Worksheets to Monitor Retirement Benefits

Using Microsoft Excel to monitor retirement benefits is the extra step you can take to ensure your investments remain in line with your retirement goals and expectations. Excel is especially useful for monitoring an IRA or 401k, where worksheets can hold data, interpret numbers and project future growth. While setup might be easier with some background knowledge of Excel 2010 or later, you can, even with limited experience, create Excel worksheets that provide the information you need to get the results you require from your retirement benefits.

Basics

Open Excel, create a new blank workbook and prepare to allow Excel to monitor the health of your retirement benefits by predicting their future value. You will accomplish this using FV, the Excel Future Value function, which you can access from the "Formulas" tab on the Excel Ribbon. Depending on how you choose to use it, FV can tell you not only how much to expect in retirement savings at the end of a specific time period but also how choosing whether to make contributions at the beginning or end of the month affects your bottom line.

Set Up

Get your most current statement and start by identifying and entering constant information into your worksheet. Enter the name of your investment and goal amount in cell A1. Enter the column labels “Interest Rate,” “Number of Periods,” “Payment,” "Present Value" and “Type” in columns A2 through E2. Enter corresponding information into cells A3 through E3. Refer to your statement to find the current interest rate for your investments -- sometimes referred to as annual yield -- and enter this as a decimal percentage, such as 0.08 for an 8 percent rate, in cell A3. Calculate the remaining number of months -- or years if you contribute annually -- during which you will be saving and enter this number in cell B3. Enter your monthly/yearly contribution in cell C3. Precede your payment amount with a negative sign to tell Excel you are paying money into the account. If you are contributing $200 per month, this would appear as -$200. Enter the current value of your retirement fund, if any, in cell D3, again preceding the amount with a negative sign to tell Excel the money is already there. Leave cell E3 blank, using it only as a placeholder to remind you to include it in the FV formula later.

Ask Questions

"Ask" Excel two questions, both of which will tell you whether you need to make some adjustments to stay in line with your retirement goals. First, enter “PV = (your current balance); Invest (your monthly contribution) at the beginning of the period for the number of periods you calculate” in cell A4. As an example, this entry could appear as “PV = $20,000; Invest $200 at 8 percent interest at the beginning of the month for 120 months.” Enter the same text into cell A7, except this time you will assume you make contributions at the end of the period. This entry could appear as “PV = $20,000; Invest $200 at 8 percent interest at the end of the month for 120 months.”

Calculate

Apply the magic of Excel formulas to see just how well your retirement benefits are performing. Assume your savings goal is $1,500,000 and your current balance is $10,000. Referring to your constant data, enter the FV formula “=FV(rate, number of periods, payment, present value, type).” In cell B5 enter the formula using “1” for the type and in cell B7 enter the formula using “0” for the type. As an example, in cell B5 enter “=FV(0.05,96,-100,-10000,1)” to see that by contributing $100 per month at the beginning of the month for 96 months the FV of investment A will be $1,306,955.56. By entering the formula “=FV(0.05,120,-100,-10000,0)” in cell B7 you will see that by contributing funds at the end of the month the FV of investment A will be $1,296,236.92.

Adjust

See how making adjustments to one constant data point or more can affect your retirement benefits. Change the monthly contribution amount in cell B5 and B7 to 125 and the number of periods to 100. Notice that by increasing your monthly contribution by $25 and increasing the investment period by four months you will increase retirement savings to $1,657,578.38 if you contribute at the beginning of the month and $1,641,265.72 if you contribute at the end, thereby exceeding your retirement savings goal. Monitor retirement benefits using this formula and make changes as necessary to retirement savings to ensure you meet your overall savings goals.