PuneinvestPuneinvest
  • Mutual Fund
  • Fixed Deposit
  • SIP Cancellation
  • Capital Gain Bond
  • Web Stories
  • Financial Planning
Reading: 6 Excel Formula Calculator for Money Management – Part I
Share
Aa
PuneinvestPuneinvest
Aa
  • Mutual Fund
  • Fixed Deposit
  • SIP Cancellation
  • Capital Gain Bond
  • Web Stories
  • Financial Planning
Search
  • Mutual Fund
  • Fixed Deposit
  • SIP Cancellation
  • Capital Gain Bond
  • Web Stories
  • Financial Planning
Follow US
© Foxiz News Network. Ruby Design Company. All Rights Reserved.
Puneinvest > Blog > Calculators > 6 Excel Formula Calculator for Money Management – Part I
Calculators

6 Excel Formula Calculator for Money Management – Part I

Last updated: 2022/12/25 at 1:03 PM
Rajendra Todkar Published May 5, 2013
Share
- Advertisement -

Investors analyse the success of their investments using a number of mathematical instruments. We’ve selected the most relevant ones and explained their function as well as how to utilise these calculators to calculate your investment returns. The investment calculator allows you to calculate the current and future worth of your investment.

Contents
Compound InterestCompound Annualized Growth Rate – (CAGR)Interest Rate of Return – (IRR)XIRRPost Tax Return

Compound Interest

I want to take a loan of Rs. 200,000 to buy a used car. How much will the car cost me at annual interest rate of 9 %  for four (4) years.

The compound interest formula can be used to calculate the total cost, which includes the loan amount and interest paid.

The amount that is actually paid for Rs 2 lakh is Rs. 2,82,316. The total amount of interest charged for borrowing Rs. 2 lakh is 82316.

- Advertisement -

Formula : Future Value = P(1 + R)^N

compound interest calculation

P : Loan Amount    R : Rate of Interest   N: time in years

compound_interest

Compound Interest Calculator Excel Sheet


Compound Annualized Growth Rate – (CAGR)

I had invested Rs. 1 lakh in a mutual fund three year back at an NAV of 16. Now the NAV is Rs. 54. How should I calculate my returns on an annual basis?

Compound annualized growth rate (CAGR) will be used here to calculate the growth over a period of time. The gain of Rs. 38  (54-16) over 3 years on the initial NAV of Rs. 16 is simple return 238%  ((54-16)*100). However, it should not be construed as 48% average return over 3 years.

Compound Annualized Growth Rate  – (CAGR)

Formula :    CAGR = (((M/I)^(1/N))-1)*100

                            CAGR  = (((54/16)^(1/3))-1)*100 

                            CAGR  =  27.54%

M : Maturity Value   I : Initial Value  N : time in year

SIP Return Calculator in Excel


Interest Rate of Return – (IRR)

 I paid Rs 18,572 every year on a money back insurance policy bought 20 year back. Every fifth year, I received Rs. 40,000 and Rs. 4.5 lakh on maturity. What was my rate of returns?

The internal rate of return (IRR) has to be calculated here.

It is the interest rate accrued on an investment that has outflows and inflows at the same regular periods.

In the Excel page type Premium Rs. 18,572 as a negative figure -18572 (Outflow)

Maturity Amount & Money back type positive figure 40,000& 4,50,000  (Inflow)

Formula :    = IRR(E6:E26)

IRR :        5.28%

Internal Rate of Return  – (IRR)

Also useful for :  checking your Endowment policy.


XIRR

I bought 500 shares on 1 Jan 2012 at Rs. 220, 100 share on 20 Mar 2012 at Rs. 185 and 50 share at Rs. 165 on 6 Jun 2012 , I sold all the 650 shares at Rs. 275 on 10 Mar 2013. What is the return on my investment ?

XIRR is used to determine the IRR when the outflows and inflows are at different periods. Calculation is similar  IRR’s. Only add Transaction date. 

In an excel sheet type out data as shown here.
Outflow figures are negative (Purchase)  and inflow figures (Sales) are positive

XIRR

Formula :  XIRR(B1:B4,C1:C4)

  Also used for : Calculating your SIP Investment, ULIP Plan, Insurance Policy or any Investment avenue.


Post Tax Return

My mother want a bank FD at 9% returns for 5 years. He pays Income Tax. What will be the returns?

The Post Tax return has to be calculate here. The idea is to know the final returns on a fully taxable income.  Interest income from the bank is taxed as per your tax slab.

Formula :   Post Tax Return :  = ROI – (ROI*TR)

Also used for : Calculating post tax return of NSCs, Post office deposit , Company Deposit, etc.


Excel Mutual Fund SIP Calculator

You Might Also Like

Download Excel Mutual Fund SIP Calculator

Sign Up For Daily Newsletter

Be keep up! Get the latest breaking news delivered straight to your inbox.

By signing up, you agree to our Terms of Use and acknowledge the data practices in our Privacy Policy. You may unsubscribe at any time.
Share this Article
Facebook Twitter Email Copy Link Print
Previous Article NCD – Neesa Technologies Ltd – 14.35%
Next Article Akshya Tritiya & Gold Investment
- Advertisement -

Latest Post

  • An Introduction to the Indian Stock Market: Understanding How it Works
  • New Fund Offer: Axis Business Cycle Fund
  • Stock Market Indices: Understanding Nifty, Sensex, and Other Indexes
  • The Time and Money You Need for 1 Crore via Mutual Fund SIP
  • BNPL vs Credit Cards: Which is Right for You?

Category

  • BNPL
  • Bonds
  • Calculators
  • Capital Gain Bond
  • Child Planning
  • Credit Card
  • Featured
  • Financial Planning
  • Fixed Deposit
  • Gold
  • health Insurance
  • IPO
  • MF SIP
  • Mutual Fund
  • Mutual Fund Overview
  • Mutual Fund Service
  • NCD
  • New Fund Offer
  • Post Office
  • Retirement Planning
  • Stock Market
  • Tax Free Bonds
  • Tax Saving Scheme

You Might Also Like

lumsum investment calculator
Calculators

Download Excel Mutual Fund SIP Calculator

November 18, 2022

© 2011 Puneinvest. All Rights Reserved.

  • Terms and Conditions
  • Disclaimer
  • Privacy Policy
  • About Us
  • Our Services
  • Contact Us

Removed from reading list

Undo
Welcome Back!

Sign in to your account

Lost your password?