Disclosure: This article is written for entertainment purposes only and should not be construed as financial or any other type of professional advice.
After you have defined your financial goals, you may want to figure out how to reach your goals. There are many variables and detours on the way to achieving your goals. But you may find basic financial calculations helpful in charting your course.
Start by identifying a dollar value for each goal; then determine how much you should set aside to reach this goal within a certain time frame. You can use Microsoft Excel or Google Sheets to perform these calculations. I’ll show you the basics.
Set a Financial Goal
To place a monetary value on your goals, determine its cost today and projected cost in the future. The two factors to consider are 1) cost today and 2) cost in the future based on inflation and other factors.
Suppose you want to save for a new car, which you anticipate needing in 10 years. Today, the list price is $25,000. And you anticipate its cost to grow by 1% each year.
You can use the Future Value (FV) function in Excel to project its cost. The elements you’ll need for the formula include:
- Rate: the rate of growth or change over time, such as the inflation rate, interest rate, or rate of return
- Number of Periods (NPER): the number of periods, typically the number of years or months, from now until you reach your goal
- Present Value (PV): today’s value of the goal in terms of a dollar amount
- Type: the method of making contributions (if any), which is defined as being deposited a) at the end of a period (value = 0) or b) the beginning of a period (value = 1).
In this situation, the formula looks like this (if you make payments at the end of the period):
=FV(rate, nper, pv, type)
=FV(inflation rate of 1%, 10 years from now until the goal, the current value of $25,000, and contributions are made at the end of the year)
The savings goal, then, is $27,615.55. In summary, start the process of reaching a goal by establishing the dollar amount of the goal.
Reach a Financial Goal
Next, consider what steps you need to take to reach a financial goal.
Project your interest rate or growth rate of your investments, how long you’ll save (number of months or years), and how much you’ve already saved. Generally, you can use this information to determine how much to save on a yearly or monthly basis.
In terms of spreadsheet calculations, the main variables in reaching a financial goal include:
- Rate: the percentage growth you expect to earn monthly or annually based on interest rates and/or anticipated investment returns
- Number of Periods (NPER): the number of periods (usually months or years) from now until you need cash to pay for your goal; during this time, you can 1) contribute to your goal and/or 2) earn interest or investment returns
- Present Value (PV): the dollar amount already saved or set aside to reach the goal
- Future Value (FV): the amount of money your goal represents
- Periodic Contributions: the amount of money you plan to contribute monthly or periodically to reach the goal
For the purpose of this illustration, we’ll calculate the periodic contribution needed to achieve the financial goal. We’ll call this periodic contribution our payment (PMT).
Generally, the rate is the most prominent and unknown variable. You may be able to lock in a guaranteed interest rate on a CD of 1-2%.
But you may want the potential to earn much more through investments; however, these returns are not guaranteed and the principal may lose value. If you consider returns from the stock market, then you could possibly get investment returns of 5-7% annually (average) over the next 10 years. For the spreadsheet calculation, let’s use 5% as the investment rate and 10 years as the number of periods.
The present value is any amount already set aside or designated for this purpose. Let’s say that we’ve already saved $1,000 to purchase a car. The future value is the number calculated as the anticipated cost of the goal: $27,615.55.
Continuing with the car buying case, the formula to determine how much to set aside each year to reach this goal looks like this:
=PMT(an estimated 5% annual rate of return, 10 years of contributions or payments toward the goal, a present value of $1,000 based on the amount already saved, future value of $27,615.55 as the desired goal, and contributions made at the end of the year)
So, you’ll need to save $2,066.06 every year for 10 years and earn a 5% return each year in order to have the money to purchase a car valued at $25,000 today and $27,615.55 in the future.
If you’d rather consider how much you need to contribute monthly to reach your goal, adjust your formula for monthly amounts: =PMT(5%/12,120,1000,-27615.55,0) which results in a monthly contribution of $167.23.
Understand Limits of Spreadsheet Calculations
There are many things that can get in the way of reaching financial goals. An uneven and unpredictable growth rate (including losses associated with stock-market investing) is one of the most obvious problems.
In addition, your ability to earn and thus contribute to your goal can influence your success. During the years in which you are saving, you may lose your job, change positions and earn less than before, or fail to experience the career and earnings advancement you had counted on.
You may also incur higher-than-expected expenses for a variety of reasons including inflation, additions to your family, or more sophisticated tastes that cost a lot to satisfy. Your unique set of circumstances may foil your attempts to set aside a certain amount of money each month.
Certain costs may rise faster than overall inflation. For example, those who have saved for their children’s education may have noticed that college expenses have increased by as much as 9.5% more than the inflation rate in some years. Projecting these costs is similar to hitting a moving target.
Right now, though, we have focused on the mechanics of saving and investing to reach a financial goal with a fixed target. After you’ve gotten a handle on these calculations, you can better understand the limits of spreadsheet planning and develop more advanced strategies for achieving your long-term financial goals.