excelgoogle-sheetsfinancexirr

Calculating XIRR result

I have an instrument that is giving me 8% interest. (Calculated as 8%/12 every month but credited once a year).

What should be the XIRR for this after 2 years.

According to me it should be 8% but for the entries I made both google sheet and excel is showing it as 17%. Why is it so ? What mistake am I doing?

93672 is the Sum of all values (positive and negative).

Dates Cashflow
1/4/2021 -3600
1/5/2021 -3600
1/6/2021 -3600
1/7/2021 -3600
1/8/2021 -3600
1/9/2021 -3600
1/10/2021 -3600
1/11/2021 -3600
1/12/2021 -3600
1/1/2022 -3600
1/2/2022 -3600
1/3/2022 -3600
1/3/2022 1872
1/4/2022 -3600
1/5/2022 -3600
1/6/2022 -3600
1/7/2022 -3600
1/8/2022 -3600
1/9/2022 -3600
1/10/2022 -3600
1/11/2022 -3600
1/12/2022 -3600
1/1/2023 -3600
1/2/2023 -3600
1/3/2023 -3600
1/3/2023 5478
1/3/2023 93750

The formula I applied is xirr(B2:B28,A2:A28) Column B is cashflow, Column A is Dates

Excel image of data

Solution

  • The question uses an incorrect method for the calculation of XIRR:

    The correct method:

    =xirr(Q2:Q26,P2:P26,0.08)

    Note: interest of $1872 and $5478 is NOT included in the XIRR calculation.

    XIRR

    snapshop

    Longhand calculation

    Interest formula in Column N: =round(M2*$G$3/12*1,)

    longhand