5 Financial Formulas In Excel For Personal Finance Calculations

These 5 financial formulas in excel can solve all your problems related to personal finance instantly. Whether you need to calculate the total amount for:

1. Retirement Planning
2. Child Education
3. Child Marriage
4. Monthly investment required for any goal
5. Rate of returns for any investments.

Trust me, you just need to be familiar with these 5 formulas in excel for personal finance calculations. In fact, most guys in corporate jobs make use of power point presentations (PPT) and excel functions for daily work. Since that’s the case, then why not learn it for your own personal finance?

12 days left of lockdown. Here are first 3 articles in a series of 15 articles (one each day) to help you become a DIY investor.

1. Emergency Funds and how to invest it?
2. Term Insurance and its importance.
3. Health insurance beyond corporate cover

Financial Formulas In Excel

In this article, we will learn the following formulas step by step to help you gain a better understanding.

1. Future Value (FV)
2. Present Value (PV)
3. Payment (PMT)
4. Internal Rate of Return (IRR)
5. Extended Internal Rate of Return (XIRR)

Before going any further with the financial formulas, let us first understand 2 basic concepts – Inflation and Growth rate.

Also Read: Retirement Planning Vs Child Education

Inflation is the rate at which the value of goods increases over time. Let me take an example.

My father used to maintain all the household expenses with a monthly expenditure of 6,500 in the year 2000. (buying wheat, rice, vegetables etc.) Now in the year 2020, I spend around 20,000 per month to buy the same amount of groceries. Therefore, my expenses have increased from 6,500 to 20,000 per month, this is known as inflation. The inflation rate is 6% in the above-mentioned case.

The growth rate is the rate at which the value of an investment increases over time. Let me take another example. Suppose, I had invested 10,000 in the year 2000. Now, the value of that 10,000 has become 61,000 in a time period of 19 years. As you can see, the growth rate is 10% in this case.

So, inflation is used to calculate expenses for cases like – If children’s higher education costs 10 Lakhs today, how much would be the value in the future? The growth rate is used to calculate the investment required either in a lump sum or in installments . Moreover, it is also used to calculate the returns of existing investments.

Note – Both inflation rate and growth rate are always assumed for future investments.

Future Value (FV)

Future value is basically the value of the current investment or current expenses in the future date.

Suppose, you want to spend 15 Lakhs for your child`s higher education in today`s cost. How much would be the value after 15 years? (Assuming the child is 2 years old and the amount is required at the age of 17)

In the above screenshot, we have assumed the rate of 8% (Inflation rate). Nper is the time period (15 years for higher education) whereas PV is the present value of child education (15 Lakhs).

So, the amount required would be around 47.6 Lakhs after a time period of 15 years assuming 8% inflation.

The value of PV is always taken as negative.

Also Read: Child Education and Marriage

The same formula can be used for your investments as well. Suppose you invest 10 Lakhs today for your child`s higher education assuming a growth rate of 10%.

So, the total amount would be around 41.8 Lakhs after a time period of 15 years.

Here, there is a deficit of 5.8 Lakhs (47.6 Lakhs -41.8 Lakhs). It is the same deficit that is required to cover the educational expenses after 15 years.

The investment required to close this deficit on a monthly or lump sum basis will be covered in the PMT formula.

This formula is used for the following calculations in personal finance. (First 3 points use inflation Whereas the last one uses growth rate).

1. Child higher education expenses in the future
2. Child Marriage expenses in the future
3. Retirement expenses in future (if my current expenses are 50,000 per month, what would be my future expenses when I  retire at age 60).
4. Value of current investments in the future.

Present Value (PV)

Present Value (PV) is usually required for 2 purposes.

How much lump sum investment would be required today to successfully achieve the future goals?

In the above example, the deficit required for the child’s higher education was 5.80 Lakhs. Assuming a 10% growth rate, how much lump sum should I invest to get 5.80 Lakhs in the future?

So, the investment required is 1.40 Lakhs assuming returns of 10%.

How much should be the retirement corpus?

This we would cover in the end.

Also Read: Fire Retirement in India

Payment (PMT)

Apart from the lump sum investments in the above example, how much monthly investment would be required to achieve 5.80 Lakhs in 15 years? (Assuming 10% returns, we will utilize the formula of PMT in excel.)

So, a monthly investment of Rs. 1400 is required.

Why have we divided 10% by 12? Because we are making monthly investments, 10% is the annual return. If you are making quarterly investments, you can divide it by 4. Similarly, if you are making half-yearly investments, you can divide it by 2.

The same is the case with NPR, the years have to be converted into months. Hence, multiplication by 12 for a whole year. If it is quarterly, it would be multiplied by 4 and by 2 in the case of half-yearly.

PMT is the formula that tells you the total amount of investment required.

Internal Rate of Returns (IRR)

If any of the insurance agents are trying to sell you a traditional insurance policy, this formula can save you. IRR tells you the returns on your investments.

Suppose, an insurance agent explains that if you invest 1 Lakh for 20 years, you’d get 40 Lakhs after 20 years. This seems to be an awesome proposition, but let’s see the internal rate of return.

So, the internal rate of return is 6.20% in this case.

This formula should only be used for insurance policies where you are paying an annual premium.

The investment amount would always be negative whereas the maturity amount would always be positive.

Extended Internal Rate of Returns (XIRR)

This formula is used when you invest in an instrument but the payments are not regular. For example, you invested 1 Lakh in stocks on December 12^th 2018 and again, 2 Lakhs on August 12^th, 2019.

Now, the value is 4.50 Lakhs on January 3^rd, 2020. It shows the returns for irregular investments.

                   

XIRR is 29% in this case.

Always remember, the investment amount would always be negative. On the other hand, the final value would always be positive.

Retirement Calculations

Retirement calculations use Future value (FV), Present value (PV) and Payment (PMT) simultaneously.

Future value is used to calculate the monthly expenses required in the future date. Present value is used for the calculation of the required retirement corpus assuming future monthly expenses. Let me explain the same.

Current monthly expenses – 50,000

Current age – 36

Retirement age – 60 (Gap – 60-36 = 24 Years)

What would be the value of 50,000 after 24 years assuming 6% inflation – 2.02 Lakhs

Now, suppose we expect to live for 25 years more after reaching the age of 60. (Life expectancy of 85 years). What would be the total corpus required?

Assuming inflation and returns both as 6% after retirement – Net Returns 0%

No excel is required – Just multiply – Monthly expenses*No. of Years*12 (12 to convert years into months) = 2.02*25*12 = 606 Lakhs = 6.06 Crores (retirement corpus).

This is the scenario where you invest your entire corpus into debt instruments to be on the safer side.

Suppose you invest some part in equity and want to assume 6% inflation and 7% returns i.e. 1% returns above inflation.

 

So, the net returns are 1%. They are divided by 12 to determine the monthly value of the returns.

No of months = 25 Years*12

And monthly expenditures = 2,02,000

The total corpus required would be 5.36 Crores.

Now that you’ve read the article, tell me if there’s anything that you can not calculate by using these financial formulas in excel, for your personal finance? Do let me know by sharing your thoughts in the comment section.