Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 90741 times)

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2802
  • Total likes: 147
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1320 on: October 08, 2019, 09:56:47 AM »
For a series of even 20 cash flows from a non compounding loan payments (p&I)every two weeks, xirr is giving very different results then IRR*20

Is xirr giving the effective yield which is incorrect here because it's simple interest?
The conversion is not XIRR = IRR * 20. It's XIRR = (1+IRR)^(365/14). The fact that it's 20 cash flows is irrelevant - the fact that each period is 14 days is relevant.

Not sure what how simple interest enters into this. XIRR gives the annualized effective yield, which is a compound interest value. IRR returns the yield per period. Unlike XIRR, IRR has no way of knowing the length of each period, and therefore has to solve for the per period value, with no way of converting it to annualized.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7172
  • Total likes: 270
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1321 on: October 08, 2019, 10:35:31 AM »


The conversion is not XIRR = IRR * 20. It's XIRR = (1+IRR)^(365/14). The fact that it's 20 cash flows is irrelevant - the fact that each period is 14 days is relevant.

Not sure what how simple interest enters into this. XIRR gives the annualized effective yield, which is a compound interest value. IRR returns the yield per period. Unlike XIRR, IRR has no way of knowing the length of each period, and therefore has to solve for the per period value, with no way of converting it to annualized.

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7172
  • Total likes: 270
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1322 on: October 08, 2019, 10:37:33 AM »

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?
If I lend money at fixed 5% interest and the payments are fully amortized over 6 months for eg. Should I use XIRR on the CF , or is the return simply 5% for 6 months?

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2802
  • Total likes: 147
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1323 on: October 08, 2019, 01:18:50 PM »

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?
If I lend money at fixed 5% interest and the payments are fully amortized over 6 months for eg. Should I use XIRR on the CF , or is the return simply 5% for 6 months?
It doesn't matter how the underlying CF's were set up. IRR is definitionally a compounded rate:
Quote from: https://en.wikipedia.org/wiki/Internal_rate_of_return#Definition
The internal rate of return on an investment or project is the "annualized effective compounded return rate" or rate of return that sets the net present value of all cash flows (both positive and negative) from the investment equal to zero.

Offline isabels55

  • DansDeals Copper Elite
  • *
  • Join Date: Sep 2019
  • Posts: 2
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Chicago
Re: Excel Help and Problems
« Reply #1324 on: October 09, 2019, 02:30:49 AM »
I've had such problems last week.