excel cashflow formula

For a project, I need to estimate the expenditure for certain tasks over the next 10 years.  The tasks have various start dates (in terms of year its first undertaken), and various frequencies (in terms of how many years until its next repeated)
The columns that I would enter data are A - Task Name, B - Cost  C - Start Date, D - Frequency (years)
I then would have columns E to N for Expenditure for Years 2012....2022.  Could someone advise what formula I would use in Columns E to N to show the expenditure in each of those years.

Thanks in advance
gh_userAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

with headers in row 1 and E1 to N1 being numbers, try in E2

=IF(MOD((E$1-YEAR($C2))/$D2,1)=0,$B2,"")

copy down and across.

see attached.

cheers, teylyn
Book4.xlsx
0
 
AgeOfEmpiresCommented:
I believe this formula needs to be adjusted slightly to account for when the start date (col C) is after the year contained in E1-N1.

Using teylyn's spreadsheet, try this formula in E2 and then copy down and to the right

=IF(AND(E$1>=YEAR($C2),(MOD((E$1-YEAR($C2))/$D2,1)=0)),$B2,"")

0
 
byundtCommented:
teylyn's formula can be simplified to:
=IF(MOD(E$1-YEAR($C2),$D2),"",$B2)

This formula takes the difference in years between E1 and C2 and divides by the frequency D2. If the remainder is greater than 0 (interpreted by the formula as TRUE), then E1 is not an anniversary year for this expenditure and an empty string (looks like a blank) is returned. If the remainder is 0 (interpreted as FALSE), then the year is an anniversary and the cost in B2 is returned.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
AgeOfEmpiresCommented:
With this formula simplification (something has always bothered me about treating a boolean value as an integer value, although I've done it many times and it's standard practice - let's just hope MS never decides not to permit this), I still contend that you need to do a check similar to my response above to make sure the start year doesn't occur after the year for the column in which you are performing the calculation.

I believe this formula still returns an expenditure for 2012 in row 2 when the start date is changed to 2014.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Brad, I thought about using it this way round, too, but I didn't for two reasons:

- I prefer it when the TRUE part of an IF statement delivers what I actually want to see as the positive result of the formula
- not everybody understands the relationship between TRUE/FALSE and 1/0, so for ease of maintenance by others, I prefer to spell out the comparison

AgeOfEmpires, you are absolutely right. I had not factored that in.

cheers, teylyn
0
 
gh_userAuthor Commented:
Thanks for quick response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.