Payments for a Certain Time - Completely Stuck

[pierce2284]

I am developing a pro-forma and need to capture different types of funding during "X" time periods. However, I am not sure the best way to do this. I am thinking an if statement but I am not sure if this would be the best approach.

As an example:

- Debt Source #1 will start in year 1 but will end in year 5, and
- Debt Source #2 will start in year 6 and end in year 12

What is the best way to tell excel to recognize Debt source #1 during years 1-5 and then switch to debt source #2?

I look forward to your help. Thank you in advance!

 

[oldbrewer]

a lookup table

year 1 source 1
year 6 source 2
year 13 no source yet

so if you lookup year 5 it will return source 1 etc etc

 

[pierce2284]

a lookup table

year 1 source 1
year 6 source 2
year 13 no source yet

so if you lookup year 5 it will return source 1 etc etc

Forgot to mention...I will not know the year the financing will occur, nor will I know the duration of each source. Will a lookup table still apply? If yes, is there an example of the coding you could provide?

 

[oldbrewer]

so you want a loan but have not yet been given it, a few months hence you are given a loan for 3 years, but you will still need a loan for year 4 onwards


I am not clear what you want - is it project loan status - ie seeking, acquired, paid off,

 

[pierce2284]

Hopefully the following will help provide additional clarification:

The project will always have financing in year 1 (Let's call this Funding Source #1). However, this financing could be for any length of time (e.g., 5, 8, 10, 11 years, etc.). So I am trying to enable Excel to recognize that Funding Source #1 source goes for "X" time (let's say 5 years, for this discussion) and switch to Funding Source #2 in year 6 (when Funding Source #1 depletes). And Funding Source #2 will extend for "Y" number of years, until it runs out and a new funding source (Funding Source #3) will be used.

Please let me know if this provided further clarification or if any additional clarification needs to be provided.

 

[oldbrewer]

[TABLE="width: 426"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]project start date[/TD]
[TD="align: right"]01/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD]funding[/TD]
[TD]amount[/TD]
[TD]expires in x years[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2015[/TD]
[TD]funding1[/TD]
[TD]$500000[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/03/2020[/TD]
[TD]funding2[/TD]
[TD]$999999[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/04/2027[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]the second and third dates are auto calculated[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]if you change the dates in the D column[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]dates in A column will automatically adjust[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]anywhere near your needs ?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pierce2284]

We are on the right track. However, for this exercise all I am concerned with is the year and not the exact date (if this is possible).

How do I get the cells to recognize when to switch from one source to the next source (once the source expires)?

 

[SpillerBD]

I'm not sure if we are calculating or reporting...
but an example
=if(and(Today()>StartDate,Today(),EndDate),Calculate,Do_Nothing)
which does a specific date calculation
For just Year maybe
=if(and(Year(Today())>=Year(StartDate),Year(Today())<=Year(EndDate)),Calculate,Do_Nothing)

Use EOMONTH to calculate EndDate from the StartDate.

 

[oldbrewer]

also if(A1>T1,"expired","")

then check if that cell has expired
but
you know when funding expires - what is the issue

 

[pierce2284]

I'm not sure if we are calculating or reporting...
but an example
=if(and(Today()>StartDate,Today(),EndDate),Calculate,Do_Nothing)
which does a specific date calculation
For just Year maybe
=if(and(Year(Today())>=Year(StartDate),Year(Today())<=Year(EndDate)),Calculate,Do_Nothing)

Use EOMONTH to calculate EndDate from the StartDate.

Thank you SpillerBD,
That is essentially what I had in mind. However, when I input this action Excel computes zeros. Here is what I have for one source:

C3 = Start Year
C4 = End Year
C5 = Amount

G1= Year 1

IF(AND(C3>=G1,C4<=G1),C5,"")

I have also tried, IF(AND(C3>=G1,C4<=G1),C5,0)

For years where C3 equals the year Excel does not pull the AMOUNT, nor does it pull the amount when C4 equals the end year. Any suggestions?