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
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
Longhand calculation
Interest formula in Column N:
=round(M2*$G$3/12*1,)