Formula to calculate payback periods

ClaireCat

New Member
Joined
Mar 16, 2011
Messages
6
Hi all,

I am trying to enter a formula to calculate how many periods it will take to payback. The formula I have works well unless someone enters additional capex at a later date - then it all goes to pot.

The original calculation is on sheet 2 - but I'm playing around with sheet 1 to try and get a formula that works.

If anyone could help, or has any suggestions, they would be very much appreciated.

https://drive.google.com/file/d/1lambPuZxLW_WneONt3EO4yPgz38p-Sfz/view?usp=sharing
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
so, are you expecting the Payback period on Sheet 1 to be Period 7?
 
Upvote 0
so, are you expecting the Payback period on Sheet 1 to be Period 7?

Yes. my old formula (sheet 2) works fine, but if I put -50 in capex in period 7, I want a formula that will manage to cope with that and still work it out.
 
Upvote 0
ok, try this


Book1
EFGHIJKLMN
7Period 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8
8
9P&L50100100100100
10
11Capex-100-100-100-50
12
13
14Cashflow-100-100-1005010010050100
15Cum-100-200-300-250-150-500100
16Negative cum?
17
18Fraction------1.00.0
19
20
21Build period4periods
22Payback periodPeriod 7
Sheet1
Cell Formulas
RangeFormula
G14=SUM(G9:G11)
G15=SUM($G9:G11)
G22{=INDEX(G7:N7,MATCH(TRUE,G15:N15>=0,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Excellent, that worked a treat.

Is is possible to get it, instead of telling me which period it becomes positive, for it to tell me how many periods it took without capex in it to become positive?

So, in this case, it would say 4?
 
Upvote 0
Excellent, that worked a treat.

Is is possible to get it, instead of telling me which period it becomes positive, for it to tell me how many periods it took without capex in it to become positive?

So, in this case, it would say 4?

not quite sure I understand the RED marked above.
could you elaborate a bit more?
 
Upvote 0
not quite sure I understand the RED marked above.
could you elaborate a bit more?

Each period that has a capex entry is a period in which we are building kit and not carrying out actual work at a client. I need a count of how many months it takes when we are not building to make our money back.

So in the above example, there are 4 months with capex in it, so I don't want to count them.
Without capex, it is period 8 in which we are not building and making money - which is 4 periods of non-build.

It's the 4 periods of non-build that I'm interested in.
 
Upvote 0
Each period that has a capex entry is a period in which we are building kit and not carrying out actual work at a client. I need a count of how many months it takes when we are not building to make our money back.

So in the above example, there are 4 months with capex in it, so I don't want to count them.
Without capex, it is period 8 in which we are not building and making money - which is 4 periods of non-build.

It's the 4 periods of non-build that I'm interested in.

sorry, it's really out of my depth re P&L, capex etc.
will this work?


Book1
EFGHIJKLMN
7Period 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8
8
9P&L50100100100100
10
11Capex-100-100-100-50
12
13
14Cashflow-100-100-1005010010050100
15Cum-100-200-300-250-150-500100
16Negative cum?
17
18Fraction------1.00.0
19
20Payback period4
Sheet1
Cell Formulas
RangeFormula
G20{=MATCH(TRUE,G15:N15>0,0)-COUNTIF(G11:N11,"<"&0)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top