Solved

excel cashflow formula

Posted on 2011-09-11
6
283 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:gh_user
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 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

by:AgeOfEmpires
AgeOfEmpires earned 250 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

by:byundt
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Expert Comment

by:AgeOfEmpires
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

by:Ingeborg Hawighorst
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

by:gh_user
ID: 36520186
Thanks for quick response
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question