A formula to calculate the amount of income received between a set of dates...

[NoodlesReed]

Hello all... OK: I am officially about to lose my brain trying to work out this formula... On the surface is it fairly straight forward... but my brain ain't what it used to be... I attach an image which shows my nightmare. Bascially I have an income stream (rent), which is at one level for a "term" period, and another level for a "reverson" period. There are start and end dates for both the term and reversion. The project overall has a start and end date outside of which the income cannot exist. The income stream is shown in an annual cash flow... I need a formula to correctly identify the correct amount of rent for the period shown in the cash flow... Sounds simple right...?!?!?! See image attached... and my clumsy formula thus far... PLEASE can someone help me...!!!! Thank you!

=IF(AND(R22>$P$8,R22<=$Q$8,R22<=$E$9),MIN($M$8/R23*(R22-Q22),($M$8/R23*($Q$8-Q22))),
IF(AND(R22>$T$8,R22<=$U$8,R22<=$E$9),MIN($O$8/R23*(R22-Q22),($O$8/R23*($U$8-R22)))))

 

[sparky2205]

Good morning,
what exactly is the issue? Were you expecting the formula to return a value of 0?
If so this will do it:
=IF(AND(R22>$P$8,R22<=$Q$8,R22<=$E$9),MIN($M$8/R23*(R22-Q22),($M$8/R23*($Q$8-Q22))),
IF(AND(R22>$T$8,R22<=$U$8,R22<=$E$9),MIN($O$8/R23*(R22-Q22),($O$8/R23*($U$8-R22))),0))

Or some other value?
The way the formula is written it can only return FALSE. This is because in both of your IF statements at least one of the arguments in your AND construct will return FALSE
AND(R22>$P$8,R22<=$Q$8,R22<=$E$9 In this case you will get AND(TRUE,FALSE,TRUE) Overall = FALSE
AND(R22>$T$8,R22<=$U$8,R22<=$E$9 In this case you will get AND(FALSE,FALSE,TRUE) Overall = FALSE
and you haven't provided a [value_if_false] argument in the formula.

You may already know this but just in case:
You can evaluate parts of a formula that are themselves formulas.
Highlight the part of the formula that you want to evaluate e.g. R22>$P$8
Press F9. This will evaluate the part of the formula you have highlighted i.e. R22>$P$8. Result = TRUE
You could also evaluate the entire AND construct i.e. AND(R22>$P$8,R22<=$Q$8,R22<=$E$9). Result = FALSE
It can make it a lot easier to see what part of a formula is causing a problem.
WORD OF WARNING: After pressing F9 DO NOT press enter. This will replace that part of the formula with the result. Always press Esc to return to the original formula.

If you require more assistance with this please give more detail of what you expected the result to be and I can take a further look.

 

[NoodlesReed]

Hi sparky2205

Thank you so much for your kind response. Yes, I was expecting a "0" return. I just plugged in your formula and it worked...

There are a few variables which I need to check now: ie when the overall project date ends, differnet void periods between the two income period sub-sets...

I can happily share with you a solution which was recently suggested to me - i have tested it and it seems to work...

=IF(AND($R$8=0,(M$22-$Q$8<365),(M$22-$Q$8>0)),($O$8/M$23)*(365-($T$8-$Q$8)),
IF(IF(M$22<$E$9,IF(AND(M$22>$P$8,M$22<=$Q$8),($M8/M$23)*SUM(M$22-L$22),
IF(M$22<$T$8,($M8/M$23)*SUM($Q$8-L$22),IF((M$22-$T$8<=365),(M$22-$T$8)*($O8/M$23),
IF(M$22<=$U$8,($O8/M$23)*SUM(M$22-L$22),($O8/M$23)*SUM($U$8-L$22))))),
IF(AND(M$22-$E$9<365,$T$8<$E$9),($O8/M$23)*($E$9-L$22),0))<0,0,
IF(M$22<$E$9,IF(AND(M$22>$P$8,M$22<=$Q$8),($M8/M$23)*SUM(M$22-L$22),
IF(M$22<$T$8,($M8/M$23)*SUM($Q$8-L$22),IF((M$22-$T$8<=365),(M$22-$T$8)*($O8/M$23),
IF(M$22<=$U$8,($O8/M$23)*SUM(M$22-L$22),($O8/M$23)*SUM($U$8-L$22))))),
IF(AND(M$22-$E$9<365,$T$8<$E$9),($O8/M$23)*($E$9-L$22),0))))

What do you reckon?

In the meantime, thank you again very much for having taken the time to look at this, and stay safe!

 

[sparky2205]

Good morning,
that's quite a formula to get your head around. I think the F9 method I mentioned above might be useful in analysing it.
The formula works, from a perspective that it gives a result (451500) based on the data in your initial screenshot but the only way to know for sure is to test it rigorously.
I work in software validation and recently set up our spreadsheet validation process.
If I were validating that spreadsheet I would sit down with the owner and develop a number of scenarios that produce known outputs.
The place to start would be the AND statements in your formula.
i.e. AND($R$8=0,(M$22-$Q$8<365),(M$22-$Q$8>0)) actually translates to:
"Void" = 0 and "Cashflow period 31/03/21" - "Term End Date" < 365 and "Cashflow period 31/03/21" - "Term End Date" > 0
You may be able to word that better but hopefully you get what I mean.
That would be your scenario, your input.
Your output would be: ($O$8/M$23)*(365-($T$8-$Q$8)) which translates to ("ERV pa"/"Days")*(365-("Reversion Start"-"Term End"))
You will know what the expected value of the output should be.
You may not need to validate but this approach would be a good way of informally testing your spreadsheet.
Building up a list of scenarios in longhand could also potentially identify a scenario that you have missed in the formula. Looking at cell references isn't our normal way of looking at things.

If I can be of further help, let me know.

 

[NoodlesReed]

Hello again

I hope all is well.

Just wanted to follow up with you... the only component the forumla you suggested didnt account for was the income if it was less than a full years worth... otherwise it is sound...

The appraisal I am putting together is ongoing... so I would be delighted to let you know if something else comes up for which your expertise would be invaluable...

In the meantime, be well.

 

[sparky2205]

Hi,
apologies, I've been offline for a while. These are strange times.
Always happy to help. As are all the good folk on here.
Any further issues, just post. Just make sure you follow the rules, i.e. no piggy backing one thread on another. It just makes it difficult for others to search if threads are mixed.
Have a good day and I hope you escape this virus hell.

 

[NoodlesReed]

Hi there

No apology necessary! What crazy times these are...!!!

Thank you again for your kind message. There have been so many times in the past I have hurt my brain dealing with Excel formula issues... I s'pose it did advance my self learning ( I have put together a fairly comprehensive investment appraisal). It is howevere an evolving project... never finished... so I would be delighted to stay in touch and seek your wise counsel when my limited talent runs out...!

Noted re the rules.

Thx again, and we well!