SynopsisExcel spreadsheets simplify personal finance management by making number crunching easy.
Various mathematical formulas are used to chart out investment plans that would help meet distinct financial requirements of individuals, families or households. A combination of factors like income, expenditure, savings, interest rate, tenure and inflation rate are used as inputs in most of the formulas. The formulas are generally assumed to be complex and most individuals refrain from doing their own calculations. However, most of the formulas that are employed in money management are based on the concepts of compounding and the time value of money, which are not difficult to comprehend. Moreover, the direct application of the formulas is relatively easy.
For example, determining the maturity value of Rs.100 after 5 years at an interest rate of 6% per annum compounded annually can be easily worked out by using the simple compound interest formula: FV=P(1+r)^n
Here, P or principal is Rs.100, r or interest rate is 6% and n or tenure is 5 years. Applying these inputs in the above formula gives Rs.133.8 as FV or the maturity value. The complexity arises when a formula needs to be applied indirectly. In the above example, if one wants to know how much time the amount will get doubled in or at what interest rate the amount will double in 5 years, the indirect application of the above formula is needed.
The complexity becomes cumbersome when periodic investments are involved. For example, if one is investing a certain amount monthly, quarterly or annually, the maturity value of such investments is worked out using annuity formulas. The direct and indirect applications of annuity formulas are relatively more intricate compared to simple compound interest formula. However, both direct and indirect applications of compound interest and annuities can be easily managed using MS Excel spreadsheets.
For our anniversary special issue, we explain four in built MS
formulas that are meant for personal finance management. These formulas can help you do your own calculations and take control of your short-term and long-term financial plans. Before delving into the formulas, be aware that Excel expects the expenditures, payments or investments to be entered with a negative sign. The resultant output will be delivered with a positive sign. Such conventions are critical for some functions (like the RATE function). Ignoring these could result in no output or error.
Determining the maturity value of investments
What would be the maturity value at the end of 15 years, if Rs.1 lakh is invested at the end of every year at 7% per annum compounded annually? Only three inputs are needed as shown here and provide the maturity value of Rs.25.12 lakh. Readers can play with the numbers by changing any of the inputs and observe the results. For example, if Rs.2 lakh is invested at the end of each year for 10 years at 7% interest rate, the maturity value at the end of 10 years will be Rs.27.63 lakh.
Inflation adjusted value of assets or expenses
What would be the inflation-adjusted value of annual tuition fees of Rs.2 lakh after 10 years, if inflation is expected to average at 5% per annum during that period? Three inputs are required (as shown in visual), but for determining the inflationadjusted value, the current fees need to be the input in ‘Pv’ box. It provides the output as Rs.3.25 lakh, which means that if inflation averages at 5% every year then the tuition fees that cost Rs.2 lakh per year at present will cost Rs.3.25 lakh a year after 10 years. Readers can change the inflation rate or time period or the amount and can see the impact of inflation on expenses. If the rate is raised to 6%, the inflation-adjusted value escalates to Rs.3.58 lakh after 10 years.
Estimating CAGR returns
CAGR or annualised return is widely used in investment management as it enables performance comparison across investment alternatives. The CAGR return of BSE Sensex in the last 3 years between 2 December 2019 and 2 December 2022 is worked out to 15.5% as shown. The Pv is the value of BSE Sensex on 2 December 2019 and Fv is the value on 2 December 2022.
Calculating SIP returns
Mutual fund SIP is a favoured investment route for investors to gain exposure to equities and debt. The SIP returns for an investment of Rs.1,000 every month in the largest equity diversified fund- Kotak Flexicap Fund from 2 January 2020 to 2 December 2022 is 18.61% (1.5% monthly). The investment is a period of 36 months and Rs.47,321 is the value of investments on 2 December 2022. Apart from the RATE function, SIP returns can also be worked out using another Excel function called IRR.
Determining interest rate at which investment value will double in specified time
The interest rate at which the value of investments will double at a specified time period requires inputs like Pv which is the current value or principal amount, Fv or the required maturity value and tenure. It can be seen that a 14.89% annualized interest rate is needed for an investment to double in a period of 5 years. One can observe by changing the tenure or Nper in the formula to see how the required interest rate changes for investments to double. At 10 years, the required annualized rate drops to 7.17%. You can also see the impact on returns if you want investments to triple or quadruple by making Fv thrice or four times the Pv with a definite time period.
Purchasing power of the rupee
Rising inflation erodes the purchasing power of a currency and decreases people’s ability to pay for goods and services. In the past 10 years, the cost inflation index has increased at an annualized rate of 4.2%. This has impacted the purchasing value of the rupee as the value of Rs.100 in 2013-14 has declined to Rs.66.3 in 2022-23. You can modify the numbers to see the impact of inflation on the rupee’s purchasing power. Both rate and Nper can be modified to understand the inflation impact. If the inflation rate is changed to 6%, there will be a bigger erosion of purchasing value of Rs.100 to Rs.55.8 in the past 10 years.
Home loan calculators are easily accessible on bank websites which allows users to determine the EMI amount on loan amount. However, what if you wish to determine the loan amount for a chosen EMI (given loan tenure and interest rate)? For example, at 9% interest rate, the EMI for a Rs.30 lakh home loan for 20 years works out to Rs.26,992. If you can afford an EMI of Rs.25,000 per month, the eligible loan amount can be worked out using the PV function. Then the eligible loan amount will be Rs.27.78 lakh. The rate and Nper are adjusted for the monthly frequency of EMI payments. One can change any of the inputs and observe the impact.
Target based investment planning or retirement planning
The function helps determine the amount one needs to invest to achieve the desired target in a defined time period at an assumed average interest rate. For example, to accumulate Rs.50 lakh in 20 years at an assumed annualized rate of 8%, the required investment would be Rs.1.09 lakh a year. One can observe the importance of tenure and interest rates in goal planning. If the same amount has to be accumulated over 25 years (change Nper to 25), the annual investment requirement falls to Rs.68,393, which depicts the importance of early investment planning. Also, if the interest rate is assumed to be 12% per annum, keeping tenure at 25 years, the annual investment requirements work out to Rs.69,393, which shows the importance of equities in the investment portfolio.
‘Rate’ is the interest rate or inflation rate, ‘Nper’ is the time period or tenure, ‘Pv’ is the principal or initial value of investment or current expenses, ‘Pmt’ is the periodic investment or periodic expenditure, ‘Fv’ is the future value or maturity value.
Share the joy of reading! Gift this story to your friends & peers with a personalized message. Gift Now