Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# excel cashflow formula

Posted on 2011-09-11
Medium Priority
299 Views
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.

0
Question by:gh_user
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 36518611
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

LVL 4

Assisted Solution

AgeOfEmpires earned 1000 total points
ID: 36519115
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

LVL 81

Expert Comment

ID: 36519141
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

LVL 4

Expert Comment

ID: 36519177
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

LVL 50

Expert Comment

ID: 36519839
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

Author Closing Comment

ID: 36520186
Thanks for quick response
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month6 days, left to enroll