C
Chad
Hey all,
Does anyone have a macro for calculating payback period??
I need some help thanks
Does anyone have a macro for calculating payback period??
I need some help thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
David
The question you ask is complicated. I hhave an Excel
file, which I tried to copy and past, but of course it is
not that simple. Many of the explanations of the formulas
are in what pasted, if you can read it. What you ask is
subject to a lot of questions, but several alternatives
are covered, if again, you can read it. There is no code
involved, only formulas.
Capital Investment evaluation.
Some general statistics: 2080 work hours in a year,
assuming no time off, which is 260
days. Generally, 2000 hours and 250 days are used for
analysis. Generally, an overhead rate
of 44% of actual rate for a full time employee, which is
supposed to cover insurance, payroll
taxes and other employee expenses. The overhead rate is
not set in stone and varies by location.
1) Pay Back Method
General: Cost of Investment
200000 = 5 Years
Annual Cash Flow 40000
a) Savings Investment 10000 Cumulative
Savings Year 1 2000 2000
Savings Year 2 3000 5000
Savings Year 3 5000 10000 Paid Back
Savings Year 4
b) Sales Investment 10000 Cumulative
Net Profit Yr 1 2000 2000
Net Profit Yr 1 3000 5000
Net Profit Yr 1 5000 10000 Paid Back
Net Profit Yr 1
This type of investment assumes that sales are the
purpose of the investment. In other words, the
investment was made with the idea that we can charge a
customer for the use of the equipment. The
net profit figure is harder to arrive at and needs to
take into account all the direct costs of utilizing the
equipment. The above sample is simplified, but is the
essence of the analysis.
Below is a more realistic approach to an analysis.
Cost of equipment is installed and includes ship-
ing and training. Other costs should be any costs
incurred because of operation and ownership. Fill in the
"Data Entry Area," and the rest will be calculated for
you. Rounding errors will occur, but should not be large,
only pennies.
The sheet is protected, which allows data entry in
the "Data Input Area", only. It can be unprotected by
selecting Tools, Protection, Unprotect Sheet, but this
should not be necessary and is not recommended,
since the rest of the sheet is formula driven.
Data Input Area: Cost of Equipment:
$21,000.00 Installed with training
Cost of Equipment $: $21,000.00 Installed
w/shipping Daily Billable Hours: 8
Per day
Billable Rate/hr:
$20.00 per/hr = $160.00/day
Daily Billable Usage : 8 Hours Used/day
Daily Revenue: $160.00 8hrs @ $20/hr
Billable Rate/hr $: $20.00 Customer Charge/hr
or Direct Costs/hr :
Billable Rate/day $: $- Customer Charge/day
Operator Cost/hr: $9.50 per/hr -
Assumed
Operator Overhead:
$4.18 44% of hourly rate
Operator Cost/hr $: $9.50 Actual hourly rate
Other Variable Costs: $1.00
Additional electric, maint, etc./hr
Operator Overhead %: 44% Ins, taxes, other
Cost/hr: $14.68 Sum of Variable
Costs/hr
Other Variable Costs $: $1.00 Electric, gas, other
Cost/day: $117.44 Daily Billable
Hours * Cost/hr
Hourly Gain(Loss):
$5.32 Billable Hr - Cost/hr
Gain/(Loss)/day $42.56
Daily Revenue - Cost/day
Pay Back in Hours:
3,947 Cost / Gain(Loss)/hr
Pay Back in Work Days:
493 Pay Back in Hours / Daily Billable Usage
Pay Back # of Years:
1.97 Pay Back in Work Days / 250
Total Revenue
$78,947.37 Income
Total Labor/O/H
$54,000.00 less expense
Total Other Costs
$3,947.37 less expense
Net $21,000.00
Over 1.97 Years
Return 50.67%
Annualized Simple not Compounded
Gross Margin
$10,640.00 After 2.97 Years, 250 * Gain/(Loss)/day
for Next 1 Year
file, which I tried to copy and past, but of course it is
not that simple. Many of the explanations of the formulas
are in what pasted, if you can read it. What you ask is
subject to a lot of questions, but several alternatives
are covered, if again, you can read it. There is no code
involved, only formulas.
Capital Investment evaluation.
Some general statistics: 2080 work hours in a year,
assuming no time off, which is 260
days. Generally, 2000 hours and 250 days are used for
analysis. Generally, an overhead rate
of 44% of actual rate for a full time employee, which is
supposed to cover insurance, payroll
taxes and other employee expenses. The overhead rate is
not set in stone and varies by location.
1) Pay Back Method
General: Cost of Investment
200000 = 5 Years
Annual Cash Flow 40000
a) Savings Investment 10000 Cumulative
Savings Year 1 2000 2000
Savings Year 2 3000 5000
Savings Year 3 5000 10000 Paid Back
Savings Year 4
b) Sales Investment 10000 Cumulative
Net Profit Yr 1 2000 2000
Net Profit Yr 1 3000 5000
Net Profit Yr 1 5000 10000 Paid Back
Net Profit Yr 1
This type of investment assumes that sales are the
purpose of the investment. In other words, the
investment was made with the idea that we can charge a
customer for the use of the equipment. The
net profit figure is harder to arrive at and needs to
take into account all the direct costs of utilizing the
equipment. The above sample is simplified, but is the
essence of the analysis.
Below is a more realistic approach to an analysis.
Cost of equipment is installed and includes ship-
ing and training. Other costs should be any costs
incurred because of operation and ownership. Fill in the
"Data Entry Area," and the rest will be calculated for
you. Rounding errors will occur, but should not be large,
only pennies.
The sheet is protected, which allows data entry in
the "Data Input Area", only. It can be unprotected by
selecting Tools, Protection, Unprotect Sheet, but this
should not be necessary and is not recommended,
since the rest of the sheet is formula driven.
Data Input Area: Cost of Equipment:
$21,000.00 Installed with training
Cost of Equipment $: $21,000.00 Installed
w/shipping Daily Billable Hours: 8
Per day
Billable Rate/hr:
$20.00 per/hr = $160.00/day
Daily Billable Usage : 8 Hours Used/day
Daily Revenue: $160.00 8hrs @ $20/hr
Billable Rate/hr $: $20.00 Customer Charge/hr
or Direct Costs/hr :
Billable Rate/day $: $- Customer Charge/day
Operator Cost/hr: $9.50 per/hr -
Assumed
Operator Overhead:
$4.18 44% of hourly rate
Operator Cost/hr $: $9.50 Actual hourly rate
Other Variable Costs: $1.00
Additional electric, maint, etc./hr
Operator Overhead %: 44% Ins, taxes, other
Cost/hr: $14.68 Sum of Variable
Costs/hr
Other Variable Costs $: $1.00 Electric, gas, other
Cost/day: $117.44 Daily Billable
Hours * Cost/hr
Hourly Gain(Loss):
$5.32 Billable Hr - Cost/hr
Gain/(Loss)/day $42.56
Daily Revenue - Cost/day
Pay Back in Hours:
3,947 Cost / Gain(Loss)/hr
Pay Back in Work Days:
493 Pay Back in Hours / Daily Billable Usage
Pay Back # of Years:
1.97 Pay Back in Work Days / 250
Total Revenue
$78,947.37 Income
Total Labor/O/H
$54,000.00 less expense
Total Other Costs
$3,947.37 less expense
Net $21,000.00
Over 1.97 Years
Return 50.67%
Annualized Simple not Compounded
Gross Margin
$10,640.00 After 2.97 Years, 250 * Gain/(Loss)/day
for Next 1 Year
N
Norman Harker
Hi Chad!
Once you've calculated PMT the payback period will be PV/-PMT
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Once you've calculated PMT the payback period will be PV/-PMT
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob Benjamin
Chad,
If you want to check if your calculation of payback periods is correct there
is an interesting table
(figure 7-2)on page 114 of the book called "Wall Street On Sale" by Timothy
P. Vick that shows the payback periods for stocks.
I have always wanted to know how that table was calculated. So
your question and the answers to it is particularly interesting to me.
Thanks for asking it.
If PBP = PV/PMT it should be relatively easy to calculate since Excel has
a PV and a PMT functions. I'll give it a try.
Cheers. BobB
If you want to check if your calculation of payback periods is correct there
is an interesting table
(figure 7-2)on page 114 of the book called "Wall Street On Sale" by Timothy
P. Vick that shows the payback periods for stocks.
I have always wanted to know how that table was calculated. So
your question and the answers to it is particularly interesting to me.
Thanks for asking it.
If PBP = PV/PMT it should be relatively easy to calculate since Excel has
a PV and a PMT functions. I'll give it a try.
Cheers. BobB
C
Crash
Here are two Functions that will work for simple cashflows.
The first one is simple, the second takes into consideration the time
value of money.
HTHs
Jon
'---------------------------------------------------------------------------------------
' Procedure : FAME_Payback
' DateTime : 08/04/2002 22:22
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes :
'---------------------------------------------------------------------------------------
'
Function FAME_Payback(Cashflows)
'Calculate the payback period
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greaterthan 0
i = 0 'Counter variable
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i)
Loop
If CumSum >= 0 Then
CumSum = CumSum - Cashflows(i)
PB = (i - 2) - CumSum / Cashflows(i)
FAME_Payback = PB
Else: FAME_Payback = "Payback > Life" 'Report error
End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : NEW_Payback
' DateTime : 08/04/2002 22:12
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes : Payback periods calculated with PV of Cashflows
'---------------------------------------------------------------------------------------
'
Function NEW_Payback(Cashflows, Rate)
'Calculate the payback period if Rate = 0, discounted payback if Rate <> 0
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greater than 0
i = 0
If Rate >= 1 Then Rate = Rate / 100
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i) / (1 + Rate) ^ (i - 1)
Loop
If CumSum >= 0 Then
CumSum = CumSum - Cashflows(i) / (1 + Rate) ^ (i - 1)
PB = (i - 2) - CumSum / (Cashflows(i) / (1 + Rate) ^ (i - 1))
NEW_Payback = PB
Else: NEW_Payback = "Payback > Life" 'Report error
End If
End Function
The first one is simple, the second takes into consideration the time
value of money.
HTHs
Jon
'---------------------------------------------------------------------------------------
' Procedure : FAME_Payback
' DateTime : 08/04/2002 22:22
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes :
'---------------------------------------------------------------------------------------
'
Function FAME_Payback(Cashflows)
'Calculate the payback period
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greaterthan 0
i = 0 'Counter variable
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i)
Loop
If CumSum >= 0 Then
CumSum = CumSum - Cashflows(i)
PB = (i - 2) - CumSum / Cashflows(i)
FAME_Payback = PB
Else: FAME_Payback = "Payback > Life" 'Report error
End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : NEW_Payback
' DateTime : 08/04/2002 22:12
' Author : Jon 'Crash' Eggett
' Purpose :
' Notes : Payback periods calculated with PV of Cashflows
'---------------------------------------------------------------------------------------
'
Function NEW_Payback(Cashflows, Rate)
'Calculate the payback period if Rate = 0, discounted payback if Rate <> 0
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when
greater than 0
i = 0
If Rate >= 1 Then Rate = Rate / 100
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i) / (1 + Rate) ^ (i - 1)
Loop
If CumSum >= 0 Then
CumSum = CumSum - Cashflows(i) / (1 + Rate) ^ (i - 1)
PB = (i - 2) - CumSum / (Cashflows(i) / (1 + Rate) ^ (i - 1))
NEW_Payback = PB
Else: NEW_Payback = "Payback > Life" 'Report error
End If
End Function