Payments for a Certain Time - Completely Stuck

pierce2284

New Member
Joined
Jul 14, 2015
Messages
5
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! :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

New Member
Joined
Jul 14, 2015
Messages
5
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

New Member
Joined
Jul 14, 2015
Messages
5
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
project start date01/04/2015
datefundingamountexpires in x years
01/04/2015funding1$5000005
31/03/2020funding2$9999997
01/04/2027
the second and third dates are auto calculated
if you change the dates in the D column
dates in A column will automatically adjust
anywhere near your needs ?

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

pierce2284

New Member
Joined
Jul 14, 2015
Messages
5
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,782
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
also if(A1>T1,"expired","")

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

pierce2284

New Member
Joined
Jul 14, 2015
Messages
5
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?
 
Last edited:

Forum statistics

Threads
1,172,024
Messages
5,878,783
Members
433,370
Latest member
mcleven

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top