Solved

excel cashflow formula

Posted on 2011-09-11
6
281 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now