# MS Excel Function: 4 MS Excel functions you can use to manage your personal finances – The Economic Times

## 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

Excel

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.

FV Function
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.

Rate Function
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.

PV Function