• Blog
  • Calculators
  • Quiz
  • Sample Financial Plan
MoneyChai
  • Blog
  • Forum
  • Android App
  • Blog
  • Calculators
  • Quiz
  • Sample Financial Plan

5 Financial Formulas In Excel For Personal Finance Calculations

By:MoneyChai Financial Planning Last Updated: 13 Apr, 2020

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?

Financial Formulas In Excel

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)

future value in excel

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%.

future value

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?

present value in excel

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.)

pmt payment formula 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.

irr-formula-in-excel

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 12th 2018 and again, 2 Lakhs on August 12th, 2019.

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

                    xirr formula in excel

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

retirement-formula-in-excel

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.

 

retirement corpus in excel

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.

 

88 Shares
Share88
Tweet
Share
WhatsApp

Filed Under: Financial Planning

MoneyChai

Hi, I am Ajay Pruthi, an alumnus of NIT Jalandhar and K.J. Somaiya Institute of Management Studies. I have over 10 years of experience in the field of insurance and have worked with top two private insurance players in the country. I am a Certified Financial Planner and currently working as a Paraplanner with Mr. Melvin Joseph, founder of Finvin Financial Planners. If you liked my blog and want to discuss further on comprehensive fee only financial planning, feel free to get in touch by visiting Finvin Financial Planners.

You May Also Like

  • Rebalancing Of Portfolio – Shifting From Equity To Debt

    Rebalancing Of Portfolio – Shifting From Equity To Debt

  • Download Sample Financial Plan – Consolidation Of Investments

    Download Sample Financial Plan – Consolidation Of Investments

  • Creating Equity Portfolio for Your Investments

    Creating Equity Portfolio for Your Investments

  • Creating Debt Portfolio For Your Investments

    Creating Debt Portfolio For Your Investments

Reader Interactions

Comments

  1. Manish says

    April 12, 2020 at 10:20 pm

    There is a mistake in your Retirement Calculations in excel file for PMT value it should be 2,02,000 in place of 20200.

    Reply
    • MoneyChai says

      April 13, 2020 at 4:34 pm

      Thanks Manish.

      Changed. Typo error.

      Reply
  2. CHANDRACHUR SAHOO says

    October 24, 2020 at 10:51 am

    HOW IS INDEXATION CALCULATED IN MUTUAL FUNDS

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • Retirement
  • Retirement
  • PF Accumulation at Retirement
  • NPER
  • Child`s Goals
  • Higher Education
  • Marriage
  • Investments
  • HRA
  • PPF Accumulation
  • PMVVY
  • SIP
  • Lumpsum Investment
  • Fixed Deposit
  • Recurring Deposit
  • Present Value of Money
  • Future Value of Money
  • Increasing SIP calculator
  • Compound Annual Growth Rate
  • Monthly SIP Required for Any Goal
  • SCSS
  • Term Insurance
  • Life Insurance Need

Newsletter

Popular posts

  • House Rent Allowance Rules and Regulations – HRA 2020-21

  • Gratuity Formula – Rules, Limit, Eligibility & Calculation 2020

  • Gift Tax Rate in India – Limits, Exemptions and Rules 2020-21

  • NRI PPF Rules – Account and Notification 2019

  • GST Rate on Real Estate – Under Construction & Completed Property

  • Should I Surrender My Jeevan Anand Policy from LIC?

  • Cost Inflation Index Calculation for FY 2020-21 & AY 2021-22

  • Early Retirement in India -How to Retire Early like Suresh ?

  • Bank Locker Rules and Regulations – Charges & RBI Guidelines

  • Financial Advisor Fees Structure, Cost & Charges in India

Recent Posts

  • Is It Wise to Mix Insurance and Investment – Never

  • Types of Risk in Debt Mutual Funds –Interest Rate, Credit and Liquidity

  • Tax on PF Interest – New PF Rules 2021 with Example

  • Calculating Returns And Lazy You

  • Deductions under Section 80C for FY 2020-21 – Tax Benefits

  • ICICI Pru Guaranteed Income for Tomorrow Review – 4%-5%Returns

  • Deduction under Section 80D – Tax Benefits for 2020-21

  • LIC Bima Jyoti Review, Returns and Tax – New LIC Plan 860 Details

  • Should You Purchase Capital Gain Bonds from NHAI & REC to Save Tax?

  • Chemist Vs Biased Doctor Vs Unbiased Doctor

Footer

About Us

Hi,
I am Ajay Pruthi, an alumnus of NIT Jalandhar and K.J. Somaiya Institute of Management Studies. I have over 10 years of experience in the field of insurance and have worked with top two private insurance players in the country.

I am a Certified Financial Planner and currently working as a Paraplanner with Mr. Melvin Joseph, founder of Finvin Financial Planners.

If you liked my blog and want to discuss further on comprehensive fee only financial planning, feel free to get in touch by visiting Finvin Financial Planners.

 

Newsletter

Help Links

  • Contact Us
  • Write for moneychai
  • Sitemap
  • Disclaimer
  • Advertise
  • Archive

Categories

  • Banking
  • Financial Planning
  • Insurance
  • Investment
  • Mutual Funds
  • Retirement
  • Tax
  • Chai Pe Charcha
  • Blog
  • Forum
  • Android App
Copyright ©2017 MoneyChai. Designed by Mount Moriah Infotechs