Uniform Annual Cost

[Dane Cooper]

I am looking to create a function that will calculate the discounted uniform
annual cost (UAC) of a cash flow series that do not necessarily begin in the
first year of the project. Right now, I have the following fields in a
table that give all the information to calculate the UAC, but I don't know
how to set it up. Here are the fields in my table:

DiscRate = Discount Rate
ProjYear = Project Year
EconLife = Economic Life
BeginYear = Beginning Year of Expenditure
EndYear = Ending Year of Expenditure
ExDescr = Description of expenditure
ExAmt = Annual Expenditure Amount

Any help on this is greatly appreciated.

Dane

 

[James A. Fortune]

I am looking to create a function that will calculate the discounted uniform
annual cost (UAC) of a cash flow series that do not necessarily begin in the
first year of the project. Right now, I have the following fields in a
table that give all the information to calculate the UAC, but I don't know
how to set it up. Here are the fields in my table:

DiscRate = Discount Rate
ProjYear = Project Year
EconLife = Economic Life
BeginYear = Beginning Year of Expenditure
EndYear = Ending Year of Expenditure
ExDescr = Description of expenditure
ExAmt = Annual Expenditure Amount

Any help on this is greatly appreciated.

Dane

Access has some built-in financial functions such as the number of
periods for an annuity (NPer). A simple trick to start an annuity in a
future year is to extend a positive and a negative annuity back to the
ProjYear:

_________________
ExAmt | | A1 = Annuity 1
| |
++++++++++++++++++++++++++++++++++++++++++>
| |
| | A2 = Annuity 2
______

equals

__________
ExAmt | |
| |
++++++++++++++++++++++++++++++++++++++++++>
PY BY EY EL

It looks like you want to convert all annuity expenditures into a sum of
present values relative to currency values at the start of each project.
The NPV function seems like a cumbersome way to do this. Let me know
if I'm getting warm. Perhaps I can derive a formula for this if you
can't do it with Access' built-in financial functions.

James A. Fortune
(e-mail address removed)

 

[Dane Cooper]

James:

You are getting warm. What I am looking to do is derive the uniform annual
cost for each item in each alternative cost stream in a project. Some of
those cost streams do not begin at the same time as the start of the
project, and they may not end at the same time either. But I need to
evaluate all cost streams in project-year-dollars. This is kind of how it
looks

Project (begins in 2008)
Status Quo Alternative
Cost Item A (begins in 2008, end in 2012)
Cost Item B (Begins in 2010, ends in 2020)
Cost Item C (Begins in 2009, ends in 2015)
Alternative 1
Cost Item D (Begins in 2008, ends in 2030)
Cost Item E (Begins in 2010, ends in 2030)

I'll then evaluate alternative 1 against the status quo. Since the two
alternatives have different economic lives, I intend to use uniform annual
cost to compare them. However, to do this, I need the sum of the discount
factors from 2010 to 2020 for Cost Item B (for example). At a discount rate
of 10%, this would equal about 7.81 - 2.74 (or roughly 5.08). As you can
see, getting Access to do the math is a challenge. (Below is the discount
factors query).

qry DiscFacCum Project Year Expense Year Discount Rate Disc Factor Cum
Disc Factor
2008 2008 10.00% 1.00 1
2008 2009 10.00% 0.91 1.90909090909091
2008 2010 10.00% 0.83 2.73553719008264
2008 2011 10.00% 0.75 3.48685199098422
2008 2012 10.00% 0.68 4.16986544634929
2008 2013 10.00% 0.62 4.79078676940845
2008 2014 10.00% 0.56 5.35526069946223
2008 2015 10.00% 0.51 5.86841881769293
2008 2016 10.00% 0.47 6.33492619790267
2008 2017 10.00% 0.42 6.75902381627515
2008 2018 10.00% 0.39 7.14456710570468
2008 2019 10.00% 0.35 7.49506100518607
2008 2020 10.00% 0.32 7.81369182289643
2008 2021 10.00% 0.29 8.10335620263312
2008 2022 10.00% 0.26 8.3666874569392
2008 2023 10.00% 0.24 8.60607950630836
2008 2024 10.00% 0.22 8.82370864209851
2008 2025 10.00% 0.20 9.02155331099864
2008 2026 10.00% 0.18 9.20141210090786
2008 2027 10.00% 0.16 9.36492009173442
2008 2028 10.00% 0.15 9.51356371975856
2008 2029 10.00% 0.14 9.6486942906896
2008 2030 10.00% 0.12 9.77154026426327


What I want my query to look like when all is said and done is something
like this:

Beg Year Ending Year Cost Item Description Quantity Unit Price
Annual Cost Cumulative Cost Present Value Cost Uniform Annual Cost
2008 2020 Personnel 20 $ 50,000.00 $ 1,000,000 $
13,000,000 $ 7,450,063 $ 753,519
2008 2037 Retirement 20 $ 20,000.00 $ 400,000
$ 12,000,000 $ 3,954,813 $ 400,000
2010 2012 Training 10 $ 15,000.00 $ 150,000 $
450,000 $ 323,335 $ 32,703
2020 2020 New Building 1 $ 1,000,000.00 $ 1,000,000 $
1,000,000 $ 303,803 $ 30,727


That might be way more information than you needed, but perhaps not. Any
help is GREATLY appreciated.

Dane

 

[James A. Fortune]

James:

You are getting warm. What I am looking to do is derive the uniform annual
cost for each item in each alternative cost stream in a project. Some of
those cost streams do not begin at the same time as the start of the
project, and they may not end at the same time either. But I need to
evaluate all cost streams in project-year-dollars. This is kind of how it
looks

Project (begins in 2008)
Status Quo Alternative
Cost Item A (begins in 2008, end in 2012)
Cost Item B (Begins in 2010, ends in 2020)
Cost Item C (Begins in 2009, ends in 2015)
Alternative 1
Cost Item D (Begins in 2008, ends in 2030)
Cost Item E (Begins in 2010, ends in 2030)

I'll then evaluate alternative 1 against the status quo. Since the two
alternatives have different economic lives, I intend to use uniform annual
cost to compare them. However, to do this, I need the sum of the discount
factors from 2010 to 2020 for Cost Item B (for example). At a discount rate
of 10%, this would equal about 7.81 - 2.74 (or roughly 5.08). As you can
see, getting Access to do the math is a challenge. (Below is the discount
factors query).

qry DiscFacCum Project Year Expense Year Discount Rate Disc Factor Cum
Disc Factor
2008 2008 10.00% 1.00 1
2008 2009 10.00% 0.91 1.90909090909091
2008 2010 10.00% 0.83 2.73553719008264
2008 2011 10.00% 0.75 3.48685199098422
2008 2012 10.00% 0.68 4.16986544634929
2008 2013 10.00% 0.62 4.79078676940845
2008 2014 10.00% 0.56 5.35526069946223
2008 2015 10.00% 0.51 5.86841881769293
2008 2016 10.00% 0.47 6.33492619790267
2008 2017 10.00% 0.42 6.75902381627515
2008 2018 10.00% 0.39 7.14456710570468
2008 2019 10.00% 0.35 7.49506100518607
2008 2020 10.00% 0.32 7.81369182289643
2008 2021 10.00% 0.29 8.10335620263312
2008 2022 10.00% 0.26 8.3666874569392
2008 2023 10.00% 0.24 8.60607950630836
2008 2024 10.00% 0.22 8.82370864209851
2008 2025 10.00% 0.20 9.02155331099864
2008 2026 10.00% 0.18 9.20141210090786
2008 2027 10.00% 0.16 9.36492009173442
2008 2028 10.00% 0.15 9.51356371975856
2008 2029 10.00% 0.14 9.6486942906896
2008 2030 10.00% 0.12 9.77154026426327


What I want my query to look like when all is said and done is something
like this:

Beg Year Ending Year Cost Item Description Quantity Unit Price
Annual Cost Cumulative Cost Present Value Cost Uniform Annual Cost
2008 2020 Personnel 20 $ 50,000.00 $ 1,000,000 $
13,000,000 $ 7,450,063 $ 753,519
2008 2037 Retirement 20 $ 20,000.00 $ 400,000
$ 12,000,000 $ 3,954,813 $ 400,000
2010 2012 Training 10 $ 15,000.00 $ 150,000 $
450,000 $ 323,335 $ 32,703
2020 2020 New Building 1 $ 1,000,000.00 $ 1,000,000 $
1,000,000 $ 303,803 $ 30,727


That might be way more information than you needed, but perhaps not. Any
help is GREATLY appreciated.

Dane

Hello Dane.

I've been away from the office but I should be able to look at your
example tonight. I'll let you know if I need any more information.

James A. Fortune
(e-mail address removed)

The ancient name for Egypt was pronounced something like Chemy or
Chemay. That name was based on the name for the dark rich soil
deposited annually by the Nile river. The word for this soil, and
extended to dark or black in general, is shown as "kam" by Sir E. A.
Wallis Budge (former keeper at the British Museum) in one of his Dover
books on reading hieroglyphs.

http://en.wikipedia.org/wiki/E._A._Wallis_Budge

This name led directly to the word Al-Chemy (alchemy) and later to the
word Chemistry. In Coptic, the name for Egypt reads as Kimi. In Greek,
the ancient name of Egypt is Chemeia.

 

[James A. Fortune]

James:

You are getting warm. What I am looking to do is derive the uniform annual
cost for each item in each alternative cost stream in a project. Some of
those cost streams do not begin at the same time as the start of the
project, and they may not end at the same time either. But I need to
evaluate all cost streams in project-year-dollars. This is kind of how it
looks

Project (begins in 2008)
Status Quo Alternative
Cost Item A (begins in 2008, end in 2012)
Cost Item B (Begins in 2010, ends in 2020)
Cost Item C (Begins in 2009, ends in 2015)
Alternative 1
Cost Item D (Begins in 2008, ends in 2030)
Cost Item E (Begins in 2010, ends in 2030)

I'll then evaluate alternative 1 against the status quo. Since the two
alternatives have different economic lives, I intend to use uniform annual
cost to compare them. However, to do this, I need the sum of the discount
factors from 2010 to 2020 for Cost Item B (for example). At a discount rate
of 10%, this would equal about 7.81 - 2.74 (or roughly 5.08). As you can
see, getting Access to do the math is a challenge. (Below is the discount
factors query).

qry DiscFacCum Project Year Expense Year Discount Rate Disc Factor Cum
Disc Factor
2008 2008 10.00% 1.00 1
2008 2009 10.00% 0.91 1.90909090909091
2008 2010 10.00% 0.83 2.73553719008264
2008 2011 10.00% 0.75 3.48685199098422
2008 2012 10.00% 0.68 4.16986544634929
2008 2013 10.00% 0.62 4.79078676940845
2008 2014 10.00% 0.56 5.35526069946223
2008 2015 10.00% 0.51 5.86841881769293
2008 2016 10.00% 0.47 6.33492619790267
2008 2017 10.00% 0.42 6.75902381627515
2008 2018 10.00% 0.39 7.14456710570468
2008 2019 10.00% 0.35 7.49506100518607
2008 2020 10.00% 0.32 7.81369182289643
2008 2021 10.00% 0.29 8.10335620263312
2008 2022 10.00% 0.26 8.3666874569392
2008 2023 10.00% 0.24 8.60607950630836
2008 2024 10.00% 0.22 8.82370864209851
2008 2025 10.00% 0.20 9.02155331099864
2008 2026 10.00% 0.18 9.20141210090786
2008 2027 10.00% 0.16 9.36492009173442
2008 2028 10.00% 0.15 9.51356371975856
2008 2029 10.00% 0.14 9.6486942906896
2008 2030 10.00% 0.12 9.77154026426327


What I want my query to look like when all is said and done is something
like this:

Beg Year Ending Year Cost Item Description Quantity Unit Price
Annual Cost Cumulative Cost Present Value Cost Uniform Annual Cost
2008 2020 Personnel 20 $ 50,000.00 $ 1,000,000 $
13,000,000 $ 7,450,063 $ 753,519
2008 2037 Retirement 20 $ 20,000.00 $ 400,000
$ 12,000,000 $ 3,954,813 $ 400,000
2010 2012 Training 10 $ 15,000.00 $ 150,000 $
450,000 $ 323,335 $ 32,703
2020 2020 New Building 1 $ 1,000,000.00 $ 1,000,000 $
1,000,000 $ 303,803 $ 30,727


That might be way more information than you needed, but perhaps not. Any
help is GREATLY appreciated.

Dane

Here are a few of my observations.

Evaluating alternative cost streams is wise.

Evaluating alternative cost streams using project-year-dollars is a
valid way to compare them.

In your schema, the various cost items need to have a foreign key to
both the Project table and the Alternative table so that the cost items
can be grouped by both project and alternative.

Your final query for comparing cost streams will show the
project-year-dollar sum for each cost stream for each project. This
query can also be constrained to specific projects or cost streams
within a project.

The key item is the calulation of the project-year-dollar contribution
of each cost item.

The good news is that you don't have to apply the various discount
factors from your discount factors query unless the discount rate
changes during the project.

The formula for converting an annuity into a present value takes the
discount factor into consideration.

When I get home tonight I'll see if I can dig out my Engineering
Economics book. It should have just the equations you need to get the
present values for the future annuity cost streams and to allocate
annual costs properly. I expect that the simple annuity trick I gave
will make the present value calculation straightforward.

Is your Cumulative Cost a total expenditure based on the time value of
the money at the time of expenditure?

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James:

You are getting warm. What I am looking to do is derive the uniform annual
cost for each item in each alternative cost stream in a project. Some of
those cost streams do not begin at the same time as the start of the
project, and they may not end at the same time either. But I need to
evaluate all cost streams in project-year-dollars. This is kind of how it
looks

Project (begins in 2008)
Status Quo Alternative
Cost Item A (begins in 2008, end in 2012)
Cost Item B (Begins in 2010, ends in 2020)
Cost Item C (Begins in 2009, ends in 2015)
Alternative 1
Cost Item D (Begins in 2008, ends in 2030)
Cost Item E (Begins in 2010, ends in 2030)

I'll then evaluate alternative 1 against the status quo. Since the two
alternatives have different economic lives, I intend to use uniform annual
cost to compare them. However, to do this, I need the sum of the discount
factors from 2010 to 2020 for Cost Item B (for example). At a discount rate
of 10%, this would equal about 7.81 - 2.74 (or roughly 5.08). As you can
see, getting Access to do the math is a challenge. (Below is the discount
factors query).

qry DiscFacCum Project Year Expense Year Discount Rate Disc Factor Cum
Disc Factor
2008 2008 10.00% 1.00 1
2008 2009 10.00% 0.91 1.90909090909091
2008 2010 10.00% 0.83 2.73553719008264
2008 2011 10.00% 0.75 3.48685199098422
2008 2012 10.00% 0.68 4.16986544634929
2008 2013 10.00% 0.62 4.79078676940845
2008 2014 10.00% 0.56 5.35526069946223
2008 2015 10.00% 0.51 5.86841881769293
2008 2016 10.00% 0.47 6.33492619790267
2008 2017 10.00% 0.42 6.75902381627515
2008 2018 10.00% 0.39 7.14456710570468
2008 2019 10.00% 0.35 7.49506100518607
2008 2020 10.00% 0.32 7.81369182289643
2008 2021 10.00% 0.29 8.10335620263312
2008 2022 10.00% 0.26 8.3666874569392
2008 2023 10.00% 0.24 8.60607950630836
2008 2024 10.00% 0.22 8.82370864209851
2008 2025 10.00% 0.20 9.02155331099864
2008 2026 10.00% 0.18 9.20141210090786
2008 2027 10.00% 0.16 9.36492009173442
2008 2028 10.00% 0.15 9.51356371975856
2008 2029 10.00% 0.14 9.6486942906896
2008 2030 10.00% 0.12 9.77154026426327


What I want my query to look like when all is said and done is something
like this:

Beg Year Ending Year Cost Item Description Quantity Unit Price
Annual Cost Cumulative Cost Present Value Cost Uniform Annual Cost
2008 2020 Personnel 20 $ 50,000.00 $ 1,000,000 $
13,000,000 $ 7,450,063 $ 753,519
2008 2037 Retirement 20 $ 20,000.00 $ 400,000
$ 12,000,000 $ 3,954,813 $ 400,000
2010 2012 Training 10 $ 15,000.00 $ 150,000 $
450,000 $ 323,335 $ 32,703
2020 2020 New Building 1 $ 1,000,000.00 $ 1,000,000 $
1,000,000 $ 303,803 $ 30,727


That might be way more information than you needed, but perhaps not. Any
help is GREATLY appreciated.

Dane

I went up in the attic and found a trunk full of books. I resisted the
urge to read them and found inside the trunk the book I was thinking of:

Engineering Economy, Sixth Edition
DeGarmo, Ernest Paul
Macmillan Publishing Co., Inc. 1979
ISBN 0-02-328160-X

This means that when I derive the formula I'll have a way to check it .

On page 99, to find present worth, P, given an annuity, A, the
multiplication factor (to multiply times A) is:

(e^[rN] - 1) / {e^[rN] * (e^r - 1)}

so

P = A * (e^[rN] - 1) / {e^[rN] * (e^r - 1)}

where

r = nominal interest rate compounded continuously
N = number of periods (years)
A = uniform series amount (occurs at the end of each year)
P = present worth

In spite of the clarity of the formula in the book, deriving this result
will not only prove the formula, it will clarify subtle issues about
where the payments are applied and how to deal with situations that
don't match the pattern exactly. I'll do that next. The derivation
sometimes gives a hint about alternate ways to do the calculation in Access.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James:

You are getting warm. What I am looking to do is derive the uniform
annual cost for each item in each alternative cost stream in a
project. Some of those cost streams do not begin at the same time as
the start of the project, and they may not end at the same time
either. But I need to evaluate all cost streams in
project-year-dollars. This is kind of how it looks

Project (begins in 2008)
Status Quo Alternative
Cost Item A (begins in 2008, end in 2012)
Cost Item B (Begins in 2010, ends in 2020)
Cost Item C (Begins in 2009, ends in 2015)
Alternative 1
Cost Item D (Begins in 2008, ends in 2030)
Cost Item E (Begins in 2010, ends in 2030)

I'll then evaluate alternative 1 against the status quo. Since the
two alternatives have different economic lives, I intend to use
uniform annual cost to compare them. However, to do this, I need the
sum of the discount factors from 2010 to 2020 for Cost Item B (for
example). At a discount rate of 10%, this would equal about 7.81 -
2.74 (or roughly 5.08). As you can see, getting Access to do the math
is a challenge. (Below is the discount factors query).

qry DiscFacCum Project Year Expense Year Discount Rate Disc Factor
Cum Disc Factor
2008 2008 10.00% 1.00 1
2008 2009 10.00% 0.91 1.90909090909091
2008 2010 10.00% 0.83 2.73553719008264
2008 2011 10.00% 0.75 3.48685199098422
2008 2012 10.00% 0.68 4.16986544634929
2008 2013 10.00% 0.62 4.79078676940845
2008 2014 10.00% 0.56 5.35526069946223
2008 2015 10.00% 0.51 5.86841881769293
2008 2016 10.00% 0.47 6.33492619790267
2008 2017 10.00% 0.42 6.75902381627515
2008 2018 10.00% 0.39 7.14456710570468
2008 2019 10.00% 0.35 7.49506100518607
2008 2020 10.00% 0.32 7.81369182289643
2008 2021 10.00% 0.29 8.10335620263312
2008 2022 10.00% 0.26 8.3666874569392
2008 2023 10.00% 0.24 8.60607950630836
2008 2024 10.00% 0.22 8.82370864209851
2008 2025 10.00% 0.20 9.02155331099864
2008 2026 10.00% 0.18 9.20141210090786
2008 2027 10.00% 0.16 9.36492009173442
2008 2028 10.00% 0.15 9.51356371975856
2008 2029 10.00% 0.14 9.6486942906896
2008 2030 10.00% 0.12 9.77154026426327


What I want my query to look like when all is said and done is
something like this:

Beg Year Ending Year Cost Item Description Quantity Unit Price
Annual Cost Cumulative Cost Present Value Cost Uniform Annual Cost
2008 2020 Personnel 20 $ 50,000.00 $
1,000,000 $ 13,000,000 $ 7,450,063 $ 753,519
2008 2037 Retirement 20 $ 20,000.00 $
400,000 $ 12,000,000 $ 3,954,813 $ 400,000
2010 2012 Training 10 $ 15,000.00 $
150,000 $ 450,000 $ 323,335 $ 32,703
2020 2020 New Building 1 $ 1,000,000.00 $
1,000,000 $ 1,000,000 $ 303,803 $ 30,727


That might be way more information than you needed, but perhaps not.
Any help is GREATLY appreciated.

Dane

I went up in the attic and found a trunk full of books. I resisted the
urge to read them and found inside the trunk the book I was thinking of:

Engineering Economy, Sixth Edition
DeGarmo, Ernest Paul
Macmillan Publishing Co., Inc. 1979
ISBN 0-02-328160-X

This means that when I derive the formula I'll have a way to check it .

On page 99, to find present worth, P, given an annuity, A, the
multiplication factor (to multiply times A) is:

(e^[rN] - 1) / {e^[rN] * (e^r - 1)}

so

P = A * (e^[rN] - 1) / {e^[rN] * (e^r - 1)}

I think I see something interesting here. Instead of applying the
negative annuity trick try this:

Calculate the present value of the future annuity and adjust the value
using the discount for just those years to convert the number calculated
back to the present value relative to the project start. I think this
would change the equation to:

P = A * (e^[rN] - 1) / {e^[r(N + M)] * (e^r - 1)}

where M is how many years after the project starts that the future
annuity begins and N is how many years the future annuity lasts:

M N
_____
| |
| |
+++++++++++++++++++++
PY PV1
PV2 FV

I think the equation is mostly just the result of a series summation
formula. For non-continuous (simple) interest compounded yearly, simply
replace e^r by 1 + r:

P = A * ((1 + r) ^ N - 1) / {(1 + r) ^ (N + M) * r}

M can be calculated from the ProjYear and BeginYear for each cost item.
N can be calculated from BeginYear and EndYear. Actually, M + N
should be the same as EndYear - ProjYear. For 10%, the DiscRate r =
0.1. 'A' looks like the annual expenditure amount ExAmt. The Exp()
function in Access can be used for the continuous compounding case. You
might have to be careful about the order of evaluation of the Exp()
functions or calculate in the log domain first to keep the expression
from blowing up. I think this is getting close to what you need, but
I'll need more feedback before continuing.

James A. Fortune
(e-mail address removed)