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 92896 times)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2806
  • Total likes: 150
  • 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: 7181
  • Total likes: 271
  • 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: 7181
  • Total likes: 271
  • 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?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2806
  • Total likes: 150
  • 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.

Offline Therebbesbocher

  • Dansdeals Bronze Elite
  • *
  • Join Date: Jun 2017
  • Posts: 39
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1325 on: October 25, 2019, 12:02:16 AM »
what kind of formula would I need if I want conditions of multiple columns to add to a total? for example, if column A is more than 10 add 1 to the total, and if its less than 5 subtract 1 from the total.
TIA

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1405
  • Total likes: 133
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1326 on: October 25, 2019, 12:08:20 AM »
what kind of formula would I need if I want conditions of multiple columns to add to a total? for example, if column A is more than 10 add 1 to the total, and if its less than 5 subtract 1 from the total.
TIA
This is a simple nested IF. Like (if the total is on column F...)
Code: [Select]
=IF(A1>10,F1+1,IF(A1<5,F1-1,"NOT DEFINED"))

Offline Therebbesbocher

  • Dansdeals Bronze Elite
  • *
  • Join Date: Jun 2017
  • Posts: 39
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1327 on: October 25, 2019, 12:41:22 AM »
This is a simple nested IF. Like (if the total is on column F...)
Code: [Select]
=IF(A1>10,F1+1,IF(A1<5,F1-1,"NOT DEFINED"))
thanks, but I tried using that but it's not working, I think because once a nestled IF reaches a "true" it stops reading so it never reaches the next column. (unless I'm mistaken)

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1405
  • Total likes: 133
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1328 on: October 25, 2019, 12:55:18 AM »
thanks, but I tried using that but it's not working, I think because once a nestled IF reaches a "true" it stops reading so it never reaches the next column. (unless I'm mistaken)
Do you need something like?
Code: [Select]
=SUM(IF(A1>10,A1+1,IF(A1<5,A1-1,"NOT DEFINED")),(IF(B1>10,B1+1,IF(B1<5,B1-1,"NOT DEFINED"))),(IF(C1>10,C1+1,IF(C1<5,C1-1,"NOT DEFINED"))))

Offline Therebbesbocher

  • Dansdeals Bronze Elite
  • *
  • Join Date: Jun 2017
  • Posts: 39
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1329 on: October 25, 2019, 01:32:58 AM »
Do you need something like?
Code: [Select]
=SUM(IF(A1>10,A1+1,IF(A1<5,A1-1,"NOT DEFINED")),(IF(B1>10,B1+1,IF(B1<5,B1-1,"NOT DEFINED"))),(IF(C1>10,C1+1,IF(C1<5,C1-1,"NOT DEFINED"))))

It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9408
  • Total likes: 322
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1330 on: October 25, 2019, 07:57:58 AM »
It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?
If it's between 5 and 10, you'll want to refer to the column from where it should copy the number in that case.
Workflowy. You won't know what you're missing until you try it.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1405
  • Total likes: 133
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1331 on: October 25, 2019, 11:29:29 AM »
It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?
Like this?
Code: [Select]
=SUM(IF(A1>10,1,IF(A1<5,-1,0)),(IF(B1>10,1,IF(B1<5,-1,0))),(IF(C1>10,1,IF(C1<5,-1,0))))

Online lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4465
  • Total likes: 224
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1332 on: October 30, 2019, 11:08:54 AM »
Been trying to figure out a simple way to have a variable number (1-9) as part of an IF statement. Maybe someone here can help.

Text that Iím looking for the IF to match can be XYZ1, XYZ2, XYZ3 etc. There will always be an XYZ followed by 1-9 that I need it to pick up.
Currently I have it set to IF XYZ & cell reference (where I put in the 1-9 that it is), and the rest of my formulas work off that. I want to cut out the manually updating the cell reference each time I need my logic to populate.

Use case is in google sheets if that opens up more options.
Wouldn't REGEXEXTRACT do it? (I didn't really chap the exact use...maybe it is too late in the day...)

Back at this.

So the current function is FIND(REGEXEXTRACT( and looks through a bunch of variables separated by pipes ("XYZ[0-9]|ABC[0-9]" etc.), to return a number. The number should be around 22-25.

I just encountered where it picked up a match (and so number) as 4, but is also in the 22-25 range. So I want that if the number returned is under 15, then it should look for the second match and give me the 22-25 number.

Can't figure out the IF(FIND(REGEXEXTRACT= <15, search again and return nth match (in this case, 2nd).

Any ideas?
Once the game is over, the king and the pawn go back in the same box.