Discounting a negative value?

[Rick]

What would be the correct way to discount a negative value...(an expense):

Like (where D13 is a negative value expensed in year 2 of an analysis and Cost_of_Capital is, well
the cost of capital):

=ABS(D13)/1+ Cost_of_Capital ^ 2

or:

=D13/1+ Cost_of_Capital ^ 2

The latter returns a negative value greater than the undiscounted one, which is not correct?? For
example if D13 = $45,000.00 then the latter formula = $40,816.33. The first formula returns
$45,000.00. Both of these formulas use a 5% Cost_of_Capital.

Rick B

 

[Norman Harker]

Hi Rick!

With the correct formulas I get the same answer (with the sign
changed)

=ABS(D13)/(1+ Cost_of_Capital )^ 2
Returns: 40816.3265306122

=D13/(1+ Cost_of_Capital) ^ 2
Returns: -40816.3265306122



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

 

[JM]

What would be the correct way to discount a negative value...(an expense):

Like (where D13 is a negative value expensed in year 2 of an analysis and Cost_of_Capital is, well
the cost of capital):

=ABS(D13)/1+ Cost_of_Capital ^ 2

or:

=D13/1+ Cost_of_Capital ^ 2

The latter returns a negative value greater than the undiscounted one, which is not correct?? For
example if D13 = $45,000.00 then the latter formula = $40,816.33. The first formula returns
$45,000.00. Both of these formulas use a 5% Cost_of_Capital.

It's not clear what you are trying to do but I suspect the problem is
related to missing brackets.

ie. the divisor needs to be "(1+5%^2)"

Otherwise, please explain what is meant by "discount a negative value". Is
this
1. Ignore (not count) negative values.
2. Calculate the rebate or reduction in value
3. Something else.

 

[Norman Harker]

Hi JM!

Divisor is (1+5%)^2

Or you can multiply the amount by (1+5%)^-2

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

 

[Rick]

Cost_of_Capital is, well


which is not correct?? For


first formula returns




It's not clear what you are trying to do but I suspect the problem is
related to missing brackets.

ie. the divisor needs to be "(1+5%^2)"

Otherwise, please explain what is meant by "discount a negative value". Is
this
1. Ignore (not count) negative values.
2. Calculate the rebate or reduction in value
3. Something else.

Thanks, JM and Norman...

....what I am trying to calculate is how much will the expense (2 years in the future) cost me in
today's money. A present value calculation, so to speak.

Now that I add the parentheses (or brackets) I get $44,887.78 is this the correct answer? I can see
that the value of the expense will be less than today's money, but how much would be needed to pay
this expense?

or...Am I just not getting this accounting stuff?

Rick

 

[Norman Harker]

Hi Rick!

It's a straightforward PV thing.

Assume the calculation is right and I invest $40,816.33 today for two
years earning 5% per annum.

In two years I will have

=40816.33*(1+.05)^2
Returns 45000

And that's how much I need.

In today's terms the future obligation to pay out 45000 must be less
than the future obligation because I am able to invest money to build
up the amount I need.

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

 

[JM]

Hi JM!

Divisor is (1+5%)^2

Or you can multiply the amount by (1+5%)^-2

You're right: I saw the problem was caused by missing brackets but I
couldn't figure out what he really intended. In retrospect, it's a standard
compound interest problem.


John