Free Microsoft Excel 2013 Quick Reference

payback period formula

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!

Post your answer or comment

comments powered by Disqus
Year Cash Flows
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


Hi, I desperately need help in figuring out a formula in excel to calculate payback and discounted payback periods with uneven cash flows.
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.

Jitesh M

Hi, I'm a newbie here so please forgive me if this has been answered a zillion times already.

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 :-)


Excel 2000

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.


Carole O

I am attempting to create a formula that will find the first cell in the Cumulative CF row that is greater than zero and match it with the value in the number column, (would be 7).
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!

Hi - I have an integated PV roof system and would be grateful for any assistance in producing a spreadsheet which will calculate a rolling payback for my PV investment. Ideally I'd also like to enter PV production readings and standard usage readings on an adhoc daily basis from which to calculate average rolling daily production and usage figures. We have a dual tariff for day and night usage.

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

Yours gratefully Martin Ladd

I have to formulas:

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


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

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

Formula 1 is easy:


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

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

Hi Guys,

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

I'm preparing a financial model and developed a sheet of Calculating the Payback period of the entire investment.

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.


I have read Execl help but can't work out how to create an excel table which
will calculate the NPV for varying selling prices

Also is there a function for calculating pay back period

Hi all,

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



I am trying to put together a worksheet to figure out some local solar rebates. I am a solar installer in CO. Anyway I have the following "tiers" under the new rebate structure:
-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.


Last night I was out with a friend of mine and he was complaining because how difficult of an Exel project he was putting together. I got sick of listening to him and told him that I could do it in my sleep. In reality, I know very little about excel and almost nothing about VBA. We made a bet on it and i have to have it e-mailed to him before he leaves for work at 5pm EST today. PLEASE HELP!!!
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
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.

Can somebody offer some help on using excel for payback analysis

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



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!!


Joined the forum after lurking for most of today, I have spent 3 hours on here searching various time calculation questions.. all to no avail!

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.


I'm generally pretty good with these things, but this one seems to be beyond my skill level.

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

Hello. I was hoping someone knows a formula that can sum up results for a specific time period and for a specific subject. Below is a small portion of my data;

******** ******************** ************************************************************************>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

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

I am having trouble with creating this one formula. Can anyone help? I have
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:

I'm trying to link two absence spreadsheets together, each worksheet being a
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!

I have date coming into my data sheet. The information is

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:


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.



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


I'm trying to link two absence spreadsheets together, each worksheet being a
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.