Excel in Personal Finance - Part 4 - Function 'XIRR'

Let us learn 5 Excel functions that are useful for personal finance

Click below to read the following:
Introduction to Excel in Personal Finance

Excel Part 1 - Function PV

Excel Part 2 - Function FV
Excel Part 3 - Function EFFECT

Function 4 - XIRR

Annual return on investments – Compounded annual growth rate CAGR using XIRR function in Excel

The SBI Magnum multi-cab scheme is taken as an example and the period of investment is four years. 

Two types of calculation are used here.

(A) When the investment is irregular. Usually most of our investor investments are in this type.

(B) When the investment is regular.

Let us compute returns using function XIRR

Used factors / variable:

Information to input: 

Using XIRR in Mutual Fund investment return calculation
Variable	Remarks
Known Variable
Value	Oue investment cash flow into mutual funds
Date	Date of investments
Guss	Assumed nearest value of return
Calculated variable
XIRR	Annualized return

(A) When the investment is irregular. Usually most of our investor investments are in this type.

(B) When the investment is regular.

In both cases, table is created in excel using the actual date and amount invested. In the case of SIP it is regular. Once done, using the XIRR function, we can feed the inputs as given in the following image and arrive at the return. XIRR will always give annualised return. Cash flow out will be marked as negative and final cash flow in or market value is shown as a positive figure.

Click the link to access the working model of the Payment template  in google sheets - You are free to change the variable and get the desired results. You can download and share with your contacts who are interested in this functions / learning excel 

Click here to read the NEXT article about using PMT function: After 5 years, Karthik plans to spend Rs 15 Lakhs for a holiday trip to Europe with this family. How much money should he invest now in monthly installments? Saving money in SIP.

Click here to read the PREVIOUS article about using EFFECT function: In the case of bank deposits, Bank gives an annual interest of 7%. Here is the variation. One bank gives interest once a year. Another bank gives interest every six months but at the same 7% annual interest rate. If another bank gives interest once every three months, then we have to choose the bank which gives interest for a short period of time. This will give higher yield, which is slightly better than 7% per annum. Let us find how yield differs from bank to bank in the above example using excel effect function.