Objective – How to calculate investment return with XIRR :
This article can be useful for Investor, Finance Professional or students . As an investor you may be investing in various class of assets ,for example equity , mutual fund, fixed deposit etc. .You may be interested to know what is average annual return you may have earned from your investment. This post will guide you , How to calculate investment return with XIRR ?
XIRR formula in excel:
XIRR is now well-known function used by NPS , Paytm Money and by similar other apps . If you are investing in equity, you want to compare its return with Fixed Deposit or with other risk free asset class . Please open excel spreadsheet and you need four types of inputs viz; Outflow and inflow with corresponding date . It means you have to provide input what amount of investment you have made and when and if you have already sold it then what is date of sale .
Notional Return or Mark to Market (M2M)
In case you are currently holding shares , you can calculate notional returns or we say in accounting term as Mark to Market (M2M) .
Example – How to calculate investment return with XIRR
You have bought 10 shares of reliance industry @ Rs. 1103 per share on 9th March, 2020 and you have sold all 10 shares @ Rs. 2230 on 26th Aug, 2021 . Now give all four input to excel. Investment or cash outflow should always mention as negative and sale of shares or cash inflow should always mention as positive. Click here to download XIRR Excel Template. Below is formula in excel :
=XIRR(H6:H7,E6:E7)
Please make sure that you give both input inflow and outflow to excel otherwise it will throw an error.
We can see that in above example, it is showing return of 62 % . You can compare it with Fixed Deposit interest rate or compare it with your expected rate of return.
As an illustration ,one can use same method for any other product to calculate XIRR , e.g. In case of Fixed Deposit, you have investment date & Value (outflow) and you will get interest income & Principal repayment (Inflow with date ). You can get exact yield on your investment using XIRR formula.
Hope you have liked our post, How to calculate investment return with XIRR in excel.
Do read our post How to do Money Management? to better manage your money and plan for saving and investment .
Disclaimer: Every effort has been made to provide quality contents to avoid any errors and omissions however it is suggested for removal of any doubts user should take professional advice or should confirm with relevant Government laws, acts, rules and notifications. FinanceFriend.in or its authors or publisher are not responsible for any loss or liability incurred by using this information.
Author
-
by
-
