Factoring in the time value of money with Excel

Too many financial decisions are made without factoring in the time value of money. Whether providing financial planning advice related to a client’s retirement, advising a client about a business investment opportunity, or calculating the present value of future lease payments, among other tasks, accounting professionals need to understand how to efficiently and accurately perform the appropriate calculations for a time-value-of-money analysis.

This article provides example scenarios and explains various approaches for calculating the time value of money using Microsoft Excel. The Excel tools discussed herein include the FV, FVSCHEDULE, PV, NPV, PMT, RATE, and NPER functions.

For the sake of simplicity, this article focuses on examples related to personal financial planning. CPAs can then apply the demonstrated functions and techniques in other scenarios. Examples include calculating the present value of long-term receivables, performing a goodwill impairment evaluation, determining the proper sale price of a bond, and estimating the internal rate of return on capital budgeting decisions.

TIME VALUE FUNCTIONS (FV AND FVSCHEDULE)

Excel’s FV and FVSCHEDULE functions can be used to calculate the future value of money, whether the application involves a lump sum (i.e., one payment or deposit) or an annuity (i.e., several equal payments or deposits made in equal intervals). These functions also can be used to determine the expected future value of a cash investment, IRA, or 401(k) account.

EXAMPLES USING FV AND FVSCHEDULE

Calculating the future value of a present single sum

Your client has $500,000 in an IRA and has asked you to estimate its value when the client reaches retirement age in eight years, assuming a 6% return each year.

Using the FV and FVSCHEDULE functions

Using the FV function as illustrated in cell B12 of the screenshot “Using the FV and FVSCHEDULE Functions,” the formula =FV(0.06,8,0,-500000) is used to calculate that the client’s IRA would grow to $796,924 by the end of eight years, assuming a 6% return per year. Note that sometimes the variables in time value functions are entered as negative numbers, such as the negative $500,000 in this scenario because it represents an investment, equivalent to an outflow of cash.

Calculating the future value of a present single sum with multiple interest rates

This example shows how to use the ­FVSCHEDULE function in Excel to calculate the future value of a present single sum allowing for a changing annual rate of return over the savings period.

Your client has $500,000 in savings with eight years left before retirement. As an investment strategy, the client would like to adjust the asset allocation of her investments over time, evolving from a more aggressive strategy during the earlier years into a more conservative investment approach as she approaches retirement age. Thus, she projects to earn a 10% annual return during the first two years of the investment period, and 8%, 6%, and 4% returns over each of the next two-year periods, respectively. By applying the FVSCHEDULE function, as shown in cell B30 of the screenshot “Using the FV and ­FVSCHEDULE Functions,” the formula =FVSCHEDULE(500000{0.1,0.1,0.08,0.08,0.06,0.06,0.04,0.04}) calculates the client’s retirement savings balance will grow to $857,593 after eight years using various interest rates over that time period.

Calculating the future value of a series of periodic deposits (annuity)

Let’s consider three examples for calculating the future value of an annuity.

Calculating future value of annuity with the FV function

Example A: Your client would like to contribute $12,000 to a retirement account at the beginning of each year for the next 20 years, earning an annual return of 6%. The formula in cell B13 in the screenshot “Calculating Future Value of Annuity With the FV Function,” =FV(0.06,20,-12000,0,1), calculates the client’s retirement account would grow to $467,913 at the end of 20 years assuming a 6% return per year. Notice that the Type is coded a 1 as the payments are made at the beginning of each year.

Example B: A more likely scenario might be for your client to make monthly deposits to his retirement account. Assume your client makes deposits of $1,000 at the end of each month for 20 years and earns 6% per year on his investments over that time. By applying the formula, as shown in cell B26 of the screenshot “Calculating Future Value of Annuity With the FV Function,” =FV(0.005,240,-1000,0,0), he would have $462,041 in his account at the end of the 20-year period. Notice that the Type is coded differently from Example A because this example represents an ordinary annuity instead of an annuity due scenario.

Example C: An even more likely scenario would see your clients already having retirement savings and asking you to project the future value of their current savings combined with additional monthly contributions. Using the information from Example B, let’s further assume that your client already has accumulated $200,000 in retirement savings. In that case, you would enter -$200,000 as the PV amount when using the FV function. The formula in cell B39 of the screenshot “Calculating Future Value of Annuity With the FV Function,” =FV(0.005,240,-1000,-200000,0), calculates the future value of your client’s savings, including the existing savings, is $1,124,082, assuming a 6% return per year.

In these examples, the CPA will review the calculations with his or her clients and evaluate if the clients are on pace to meet their retirement goals. For example, the CPA may need to advise the clients to increase their savings rate, postpone their retirement age, or alter their investment strategy to meet their lifestyle plans in retirement. Excel easily can be modified to accommodate such a What-If analysis.

TIME VALUE FUNCTIONS (PV AND NPV)

The PV function in Excel allows users to determine how much future cash flows are worth in today’s dollars, whether the application involves a lump sum or an annuity. This concept is used when trying to determine today’s value of the cost of an asset that is to be paid for in the future or to calculate monthly payments for a loan, among other examples. The NPV function can be used when calculating the present value of unequal future cash flows.

EXAMPLES USING PV AND NPV

Calculating the present value of a future single sum

Example A: A client has a desired retirement savings goal of $2 million to be achieved seven years from now. She plans on making only one deposit into her account, and an annual return of 6% per year is expected. By applying the PV function, the formula in cell B12 of the screenshot “Using the PV Function,” =PV(0.06,7,0,-2000000), calculates that the client needs to deposit $1,330,114 in her retirement account today to achieve her goal of $2 million in seven years.

Using the PV function

The examples provided thus far illustrate the use of Excel’s time-value-of-money financial functions in providing financial planning services for clients. Excel’s financial functions also can be useful for the CPA in providing management advisory and assurance services for clients, either in advising them on how to record transactions, or alternatively, in evaluating the appropriateness of a client’s accounting records and financial statements. Example B below provides one such example.

Example B: A client purchased equipment and signed a note promising to pay the equipment manufacturer $100,000 at the end of four years. The client asked you to help determine the amount that should be recorded as the cost of the equipment, assuming the cost of capital (i.e., the annual discount interest rate) is 6%. The result of the formula in cell B24 of the screenshot “Using the PV Function,” =PV(0.06,4,0,-100000), indicates the cost of the equipment should be recorded as $79,209.

Calculating the present value of a series of equal payments (annuity)

Instead of making a single payment for equipment purchases, a client may have to make installment payments.

Example A: Your client signed a lease agreement for equipment, and you need to evaluate if the client capitalized an appropriate amount related to the lease. The agreement requires four annual lease payments of $25,000 at the beginning of each year, and an 8% annual interest rate is appropriate. The result of the formula in cell B13 of the screenshot “PV Function vs. NPV Function,” =PV(0.08,4,-25000,0,1), shows the leased asset should be capitalized for $89,427.

PV function vs. NPV function

Calculating the present value of a series of unequal payments

In many situations, a client might have periodic but unequal payments. If payments are not equal, the PV function in Excel cannot be used to solve the problem efficiently. However, the NPV function (i.e., net present value) does allow for unequal payments.

Example B: Assume the same scenario as Example A, except the lease agreement calls for the lease payments to increase by $1,000 each year. By applying the appropriate NPV function for this scenario, =NPV(0.08,26000,27000,28000)+25000, you can calculate that the lease would be capitalized for $94,450, as shown in cell B26 of the screenshot “PV Function vs. NPV Function.”

TIME VALUE FUNCTION (PMT)

Excel’s PMT function can be applied when assisting a client to determine the amount of monthly contributions to achieve a retirement goal or calculate monthly payments to retire a loan obligation, among other examples.

EXAMPLES USING PMT

Calculating annual and monthly payments

Examples A and B: Your client desires a balance of $1 million in a retirement account by the end of 20 years, and you project an annual return of 6% on investments. To determine how your client can reach this goal, the formula =PMT(0.06,20,0,-1000000,0) can be applied. As shown in cell B13 of the screenshot “Using the PMT Function,” the client’s required annual contribution at the end of each year would be $27,185. Alternatively, if the client already has $100,000 in existing retirement savings, cell B26 of the screenshot reveals the required annual deposit would be only $18,466, calculated by applying the formula =PMT(0.06,20,100000,-1000000,0).

Reviewing such calculations can be very eye-opening with younger clients who are just starting to save for retirement. This analysis can show them the value of starting their retirement savings early to reach their goals.

Using the PMT function

Example C: Your client is buying a house for $300,000 and would like to determine the amount of the monthly mortgage payment. The client can obtain financing for $240,000 over a 30-year period with a fixed annual interest rate of 4%. By applying the PMT function with the formula =PMT(0.00333,360,-240000,0,0), you can calculate that the monthly mortgage payment for your client would be $1,146, as shown in cell B39 of the screenshot “Using the PMT Function.” If the calculated monthly payment is beyond your client’s budgeted amount, the PMT function in Excel can be used to perform a simple What-If analysis to discuss alternative options for your client. For example, perhaps you could persuade your client to negotiate a lower purchase price, seek an alternative house within budget, or secure a lower mortgage interest rate.

TIME VALUE FUNCTION (RATE)

The RATE function in Excel enables you to calculate the annual rate of return or interest rate related to a time-value-of-money scenario.

EXAMPLES USING RATE

Calculating annual and monthly interest rates

Examples A and B: Your client desires to have a retirement account balance of $2 million in 25 years. The client plans on making monthly deposits of $1,500 and would like to know the annual return required to reach her goal. Example A in the screenshot “Using the RATE Function” illustrates how to calculate the monthly and annual return, assuming deposits into her account are made at the end of each month. Applying the formula in cell B13, =RATE(300,1500,0,-2000000,0), reveals that a monthly return of 0.836% (or an annual return of 10.03%) would be required for the client to reach a retirement goal of $2 million in 25 years. Alternatively, if the client already has $100,000 in retirement savings, the formula =RATE(300,1500,100000,-2000000,0) is applied in cell B28 to calculate that she only would need to earn a monthly return of 0.633% (or an annual return of 7.60%), to reach her goal.

Using the RATE function

Again, you could perform a simple What-If analysis in Excel when reviewing retirement plans with the client. For example, if you and your client agree that earning a 10% annual return is unrealistic over the investment period, then alternative options can be explored. For example, perhaps your client will need to consider liquidating some assets, such as selling a motorcycle that she rarely rides, and invest those proceeds in her retirement account now so the monthly or annual return she will need to earn is reduced.

Example C: A client has a budget of $1,600 for a monthly mortgage payment and is considering a home purchase that would require a mortgage of $325,000. He would like to calculate the annual mortgage interest rate he must obtain to purchase the house with a 30-year mortgage. The formula =RATE(360,1600,-325000,0,0) is applied in cell B43 of the screenshot “Using the RATE Function” to calculate the required monthly rate of 0.355%, or an annual interest rate of 4.26%, to meet his budget.

TIME VALUE FUNCTION (NPER)

If your client wants to determine how many months or years it will take to achieve a retirement cash goal or calculate the number of months or years it will take to pay off an existing financial obligation, the NPER function in Excel can be applied.

EXAMPLES USING NPER

Calculating the number of months or years

Examples A and B: A client wants to know how long it would take for her to achieve a retirement balance of $1 million if she plans on contributing $1,250 per month and will earn a 10% annual return (or 0.8333% monthly return). The formula =NPER(0.008333,1250,0,-1000000,1) is applied in cell B13 of the screenshot “Using the NPER Function” to calculate that it would take over 244 months, or about 20 years and five months, to reach her retirement goal. If the client already had $100,000 in existing retirement savings, her goal would be reached in about 183 months, or 15 years and four months, as shown in cell B28 and calculated by applying the formula =NPER(0.008333,1250,100000,-1000000,1).

Using the NPER function

Example C: Assume the client referred to in Example C of the screenshot “Using the PMT Function” would like to pay off her 4%, $240,000 mortgage faster than its 30-year term. She plans to pay an extra $100 per month above the required payment of $1,146, and wants to know by how many months her mortgage term would be reduced. By applying the formula =NPER(0.003333,1246,-240000,0,0), cell B43 of the screenshot “Using the NPER Function” reveals that by paying an extra $100 per month, she would make a total of 309 monthly payments, reducing her mortgage term by four years and three months.

Again, each of the examples above can lead to a conversation between you and your clients related to prudent financial planning. For example, if your client is overly ambitious in planning to earn a return of 10% per year during her investment period, this might delay her retirement date if the actual return rate is less. Excel can help you efficiently demonstrate to clients how plans would change if assumptions are altered. This What-If analysis can also help illustrate to clients the value in not delaying retirement investing as well as the benefits of making extra payments on their mortgages.

CONCLUSION

Excel is a powerful tool that can be used to analyze various time-value-of-money applications. Though this article primarily focuses on personal financial planning use cases, accountants can apply the techniques and Excel functions illustrated in this article to analyze business situations as well.

Accounting professionals should keep in mind that life and business don’t happen in Excel. Adjustments must continually be made to account for the changes that life brings. For example, in personal financial planning, changes in client income, lifestyle, health, etc., would require revisions to the client plans laid out in Excel. While a discussion of all circumstances that could arise is beyond the scope of this technology workshop, Excel’s time-value-of-money functions can handle many more complex situations.

About the authors

Daniel R. Brickner and Lois S. Mahoney are professors of accounting in the Department of Accounting and Finance at Eastern Michigan University in Ypsilanti, Mich. To comment on this article or to suggest an idea for another article, contact Jeff Drew, senior editor, at Jeff.Drew@aicpa-cima.com or 919-402-4056.

AICPA resources

Article

• “Microsoft Office: Ways to Master Excel and Word on Your Own,” JofA, Sept. 2017

CPE self-study

• PFP Boot Camp: Calculator and Time Value of Money (#166210, online access; #GT-PFSE6, group pricing)

Conference

• Practitioners Symposium and Tech+ Conference at AICPA Engage, June 11—14, Las Vegas

Webcasts

• Excel for Accounting Professionals Webcast Series (#VCL2EXAP1802)
• “KPI Dashboards — The New Financial Reporting Model” (#VCXKPIDB028)
• “Mastering Data Preparation and Analysis in Excel” (#VCXEXCL030)

For more information or to make a purchase or register, go to aicpastore.com or call the Institute at 888-777-7077.