Hi all,

I am trying to calculate the payback period for a series of cash flows. I have 5 years of cumulative cash flows from cells b24:f24 with the first year's cash flow being negative.

What is the right formula for calculating the payback period?

B24: -94,352

C24: 97,944

D24: 286,081

E24: 469,891

F24: 649,201

Thanks in advance!

I am trying to calculate the payback period for a series of cash flows. I have 5 years of cumulative cash flows from cells b24:f24 with the first year's cash flow being negative.

What is the right formula for calculating the payback period?

B24: -94,352

C24: 97,944

D24: 286,081

E24: 469,891

F24: 649,201

Thanks in advance!

- Discounted payback period
- Payback & Discounted Formulas For Uneven Cash Flows
- Payback Period in Excel
- Billing Period formula, kind of?
- Formula Template
- Find and match cell value greater than zero
- Rolling payback date for Photovoltaic panel investment
- Difficult array formula
- 90 Day Rolling Period help with INDEX?
- Calculating the exact Value
- How to create data tables and is there a payback period function
- Loan over month cashflow
- Rebate calculator
- VBA Code for Financial Function Userforms
- Payback analysis - Is there an elegant formula??
- Formula calculates # of months in a calendar year over a multi year period
- Formula for (Drivers) rest period when more than one day rest is involved.
- Tricky array formula - average employees within period
- Formula that calculates results for a specific time period
- Identify month in a period and run a formula
- Formula for calculating 12 month rolling period
- Formula Question:Column A Period
- Formula count occurences in a specific time period
- Formula for calculating 12 month rolling period

0 -200000

1 90000

2 65000

3 35000

4 50000

opportunity cost of capital 8%

what is discounted payback period?

I have to do this question in Excel but i don't know all the steps. Can you please tell me what formulas to use

thanks!

EX.

Yr CF

0 ($2,000)

1 160

2 200

3 350

4 395

5 432

6 440

7 442

8 444

Any help would be appreciated

I am an environmental Professional looking to invest in a green technology. I wanted to get the payback period. How can i calculate it in an excel.

regards

Jitesh M

Here's the problem. Imagine that over 10 years there are 60 x 2-month periods starting on the 1st day of every even month and ending on the last day of the following odd month (after which we name it).

Given a columnar list of dates that invoices were raised, how can I put (ideally in an adjacent column) which of those 60 2-month periods the invoice fell in?

For example: If an invoice was raised on 19/12/06 it belongs in the 31/01/07 billing period. If an invoice was raised on 05/07/11 it belongs in the 31/07/11 billing period.

Such a thing must be simple but I can't figure out how to do it and assume it must require a macro? I'm not a programmer - until now future and present value formulas are about as fancy as I've got. I've got a bazillion of these invoices to sort and don't want to do them all one by one if I can avoid it :-)

Thanks,

Megan

Is there a formula template that has the format for Net Present Value,

Internal Rate of Return, Discounted Payback Period, and Modified Internal

Rate of Return? Looking for something where the data would be entered in the

formula to produce the answer.

TIA,

Carole O

Then go back to the previous cell in this row and match that with the number in the first row (would be 6)

and then add to this

The absolute value of that value that was found that matched with the number 6 from above in the cumulative CF column (would be 20) divided by the cash flow corresponding to the value in the number column (7) from above (would be 330)

So it would look up the values to compute... 6+(20/330) = 6.06

Basically computing the payback period.

number Cash Flow Cumulative CF

0 -2000 -2000

1 330 -1670

2 330 -1340

3 330 -1010

4 330 -680

5 330 -350

6 330 -20

7 330 310

8 1000 1310

Any help would be great! thanks!

To recap, I'd like to glean a " projected payback period" or " projected paid date ", and or " return on capital".

Yours gratefully Martin Ladd

1) 1 = C/252*days(1)*d(1) + c/252*days(2)*d(2) + c/252*days(3)*d(3) +

d3

and

2) 1 = (1+C)^(days(1)/252)*d(1) + (1+C)^(days(2)1/252)*d(2) +

(1+C)^(days(3)/252)*d(3) + d(3)

days and d are ranges representing days and discount factors

respectively.

I would like to solve for C, the coupon that is paid in each period.

Formula 1 is easy:

=252*(1-B3)/sum(A1:A3*B1:B3)

where range B1:B3 is the d() array and A1:A3 is the days() array.

Now a formula for equation 2..........

I need help with a 90 rolling period formula. Ive tried to use Index but can quite come up with the right formula for it. I have attached a sample of the sheet.

What I need is whenever I input a date into Column A I need it to calculate rolling down the sheet if there is an infraction which will give me the number in the red column. This needs to keep going down the sheet every time there is another date/Infraction if it falls in the 90 days.

Thanks for the help

Mikeydaman

I'm enclsoing a payback period sheet for reference and help on the formula I have already developed. The Required solutiion is also mentioned in the spread sheet.

Looking forward for guidance from experts.

Regards,

will calculate the NPV for varying selling prices

Also is there a function for calculating pay back period

I've looked around the forum and can't find exactly what I'm looking for so hopefully someone might be able to help. Here's the problem...

I am putting a cashflow forecast together and part of this is working out how a compound loan could effect cashflow at any given period over the next 5 years. I will be constatnly buying music equipment and so will be taking out several seperate loans throughout the forthcoming years.

I need two things:

1) Say I decide to input in my cashflow that I will take out a £1000 loan in January 2011. I've worked out what the monthly fixed payments would be and have a drop down box that allows me to choose if the payback period is 12, 24, 36 or 48 months. (the payments obviously change as I change the payment period)

SOLUTION NEEDED: I need it to do the following: When I select 12 months it copies the monthly fixed payment into the next 12 months worth of cells. Likewise if I selcted 24 months it then copies it across 24 months worth of cells and so on.

What do I do to do this?

2) If I took another loan out in June 2011 (6 months after the first loan) I would still be paying off the previous loan as well as described in 1). So... is it possible that if I added the second loan in June 2011 for a payment period of 24 months (or whatever time period I chose from the drop down box) it includes the second months figure to what is already needed to be paid from the first loan.

We were looking to not add a new row each time we took another loan as we could be taking out many loans over the forthcoming years.

Hope I have explained myself.

Look forward to your thoughts

Thanks

Ed

-Small customer owned system 3KW and under: $1/watt and $.0945 per kWh produced over 9 years

-Small customer owned 3.01KW-6KW: $.75/watt and $.0945 per kWh produced

-Small systems 6.01-10KW: $4500 rebate and $.0945 per kWh produced

This I have all figured out on my spreadsheet, and it all works to show payback period, money saved, tax write off, etc.

The problem I have is I would like to use the same worksheet to do the other rebate tiers. The other two categories have a total of 5 tiers (different rebate structure). I was thinking of having a cell that I type in which category it is, and then having the cell that has my equations in it figure out which equation to use....but have no idea how to do that. ANY HELP WOULD BE APPRECIATED!!! I attached a locked worksheet to view.

Thanks,

Jeff

I must...

Create a multipages form.

Page one: Finance

For page 1, create sub procedures to calculate:

1. PMT

2. Loan duration

3. Interest Rate

4. Present value

5. Future value

Page 2: Depreciation

Calculate the depreciation function.

Page 3: Profitability

Calculate:

1. NPV

2. IRR

3. ROI

4. Payback period

He knew I wasn't going to be able to complete it so he sent me an e-mail with the attached pictures this morning of his finished product.

Say a period of 10 years with different negative cashflows in the early years and positive cashflows in the later years. Is there an elegant formula which tells me the the sum of the negative cashflows in the early

years just equals the positive cash flows in the later years so that i know the payback immediately?

Eg. Year 1,2,3,4,5,6...10

CF: -10,-20,-30,20,30,50 ... --> i would have used the IF function whereby in words, if the sum of the first year cash flows>0, return the year above (as year 1), if not add the next year cashflows and see if the sum is greater than zero, if not add the next year....until ten years.

As a result the formula becomes a chunky piece of IF function

Thanks!

Can someone please help me with a formula that will calculate the number of months in a calendar year over a multiple year period?

Please see the attachment.

I have a date range i.e. 1/1/12-8/31/13 that is 20 months long.

I'd like to have a formula that will calculate that there are 12 calendar months in 2012 and there are 8 months in 2013.

Thanks so much!!

Brett

I am a HGV driver and am trying to develop a worksheet to help me keep track of my working, driving and rest hours. Column A is Date, D is start time (hh:mm), E is finish time (hh:mm) and R is Rest. Formula in R is =(A2+D2)-(A1+E1) and this is copied down the column and is working for me IF I have a finish time in E for every day in A.

I need an entry for every day of the year to cover my rest periods as these have to be accounted for too. The problem I have is that if I don't work on a particular day, then the last entry in E will be empty and thefore cannot be used for the formula in R. I might not work for a whole week sometimes but I presume the principal would be the same, use the next actual entry in E rather than the possibly blank one I am currently specifying in the formula.

I don't know enough to decypher the formulas so I can't try to build one. Also, I am now away for a couple of days so won't be able to answer requests for further info until Thursday PM (UK time). I can post the worksheet if it will help but not until then. Thanks in anticipation for any assistance.

BilboBaggins

I have a list of employees, which has hire dates and termination dates. These are named HireDates and TermDates. I am calculating some turnover stats for our HR department. Here are some formulas I have so far:

Turnover Stats

CDE2From Date1/1/2007 3To Date12/31/2007 4 5Hired348Net Hires6Terminated259897Avg LOE 8 9Active (begin)594Net Gain10Active (end)68389

Spreadsheet FormulasCellFormulaD5=SUMPRODUCT(--(HireDates>='Turnover Stats'!$D$2),--(HireDates=$D$2),--(TermDates

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC18=

ABCDE1DateRegionRepSubjectGrade23/1/2008NewÂ YorkJonesMarketingPass33/2/2008NewÂ YorkKivellDemographicsPass43/3/2008NewÂ YorkJardineMarketingPass53/4/2008NewÂ YorkGillSalesPass63/5/2008SanÂ FranciscoSorvinoMarketingFail74/1/2008SanÂ FranciscoJonesDemographicsPass84/2/2008SanÂ FranciscoAndrewsMarketingFail94/3/2008SanÂ FranciscoJardineMarketingFail104/4/2008SanÂ FranciscoThompsonMarketingFail114/5/2008ChicagoJonesDemographicsFail124/6/2008ChicagoMorganMarketingPass134/7/2008ChicagoHowardDemographicsFail145/1/2008ChicagoParentDemographicsPass155/2/2008ChicagoJonesMarketingFail165/3/2008ChicagoSmithDeskPass175/4/2008ChicagoJonesSalesÂ SetFail185/5/2008DenverMorganDemographicsFail195/6/2008DenverJonesSalesFail206/1/2008DenverParentSalesFail216/2/2008DenverKivellSalesÂ SetFail226/3/2008Washington,Â DCSmithMarketingPass236/4/2008Washington,Â DCParentSalesÂ SetPass246/5/2008Washington,Â DCGillDemographicsPass256/6/2008OntarioSmithDemographicsFail267/1/2008QuebecJonesDemographicsFail277/2/2008AlbertaSorvinoDemographicsFail287/3/2008OntarioJardineSalesÂ SetPassSheet1Â

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

From the above data, I would need to be able to determine how many Pass, Fails, and Incompletes there were for the Last 7 Days, Last 30 Days, Last 90 Days, Last Year, and all since start. I would also need to calculate how Pass, Fails, and Incompletes there are per Subject. I need the formulas to calculate daily so the results are updated automatically in the sheet below;

******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA11=

ABCDE1OverallÂ RecordsÂ byÂ TimeÂ Frame2TimeÂ FramePassFailIncompletePercentage3YesterdayÂ Â Â Â 4PastÂ 7Â DaysÂ Â Â Â 5PastÂ 30Â DaysÂ Â Â Â 6PastÂ 90Â DaysÂ Â Â Â 7PastÂ YearÂ Â Â Â 8RecordÂ SinceÂ StartÂ Â Â Â 9Â Â Â Â Â 10OverallÂ RecordsÂ byÂ Subject11SubjectPassFailIncompletePercentage12MarketingÂ Â Â Â 13DemographicsÂ Â Â Â 14DeskÂ Â Â Â 15SalesÂ SetÂ Â Â Â 16SalesÂ Â Â Â Sheet1Â

[HtmlMaker 2.42] To see the form

two columns of dates (start and finish dates). I would like to identify a

month (e.g April)that occurs in the period between start and finish and then

if the month of April does occur in the period, calculate the number of

months between start and finish and divide by a constant (a figure saved on a

different sheet). So far I have this:

=IF(AND(("01/04/2005">=MIN(I20:I23,H20:H23)),"31/04/2005"

separate month. I need a formula to calculate the absences on a rolling 12

month period, therefore, each time I input the current month's figures, it

adds them to the 12 month rolling total but takes one month off the beginning

of the 12 month period!

Column A Period

Column B Department (number code)

Column C Internal Code

Column D Department

Column E Amount

I have individual department spreadsheets for the 20 different departments. On each spreadsheet in column c is a list of accounts.

Department code number is in a1

Accounts are listed in column c:

60010

60020

60030

60040

etc...

I want in column e a sum of the total that would apply to the department and account number.

Could you help me write a formula for column e that would add this up if the two arguements apply.

Thanks,

Rob

I am near enough ignorant about excel (and not much better with computers in general) and was hoping someone would be kind enough to help me. Obviously if this has been answered before - apologies for wasting time and a reply with a link would be appreciated.

I am a nurse trying to show how many patients were in our post anaesthetic care unit (PACU) during a specific time

I've got an into PACU time / Out of PACU time, I have managed to get a difference for which I can use when I'm looking at length of stay - but I need a formula to calculate how many patients were in PACU during a set period of time i.e. i.e. 08.00 - 09.00, 09.01 - 10.00, 10.01 - 11.00 and so on .....

This is the data I have

Hospital Number / Date / Theatre / Hour / Time into PACU / Time Out of PACU

Any help / info / formulas would be greatly appreciated - obviously in very simple terms considering my low computer IQ

Thanks

Sarah

separate month. I need a formula to calculate the absences on a rolling 12

month period, therefore, each time I input the current month's figures, it

adds them to the 12 month rolling total but takes one month off the beginning

of the 12 month period!

No luck finding an answer? You could always try Google.