0

It will help to have a field that will show annualized return on investment for lender (investor) as I borrow and repay to the lender.

enter image description here

I am advised to use XLRR function, which I did:

enter image description here

Getting #VALUE! error.

It is instructed in the Excel manual: If any number in dates is not a valid date, XIRR returns the #VALUE! error value.

I have checked data type of dates and it is date type.

Improve this question
1
  • 1
    please add a clear, answerable question to your post
    – jsotola
    Commented Mar 19, 2023 at 6:41

1 Answer 1

Reset to default
1

The computation of an internal interest rate (IRR) is based of the entire sequence of cash flows during the considered period. There must be a positive and a negative number in the sequence. In your case, you can only compute IRR after 24/08/2022 to be mathematically computable, but it does not make sense from a financial point of view.

Try with a simpler cash flow sequence of a 10% yield 3 years bond of USD100 value:

2023 -100 (you buy the bond)
2024 +10 (1 year coupon)
2025 +10 (2nd year coupon)
2026 +110 (last year coupon + principal repayment)

If you compute the IRR for the complete 2023-2026 sequence you'll find 10% as expected.

But if you compute it on the 2023-2025 you'll obtain -63%! (it is a valid number only in case of complete default)

And if you try to compute on 2023 only you'll get an error.

Improve this answer
2
  • It helped. Is there any other function that could help me what I actually need?
    – Splendid Digital Solutions
    Commented Mar 19, 2023 at 12:56
  • 1
    IRR is well adapted to complex cash flows, but you need to have the complete sequence (or a given time horizon eg IRR over 40 years for infrastructure). Otherwise, given your cash flows are not regular, you'll need to keep track for a given period of both capital and interest, and then compute the return for this given period, ie interest/capital adjusted for period duration (there maybe an excel for that)
    – AlexVal
    Commented Mar 19, 2023 at 16:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .