Posted by Denis Borris on September 20, 2002 at 15:18:17:
In Reply to: internatl rate of return (IRR) posted by bana on September 20, 2002 at 08:04:35:
: Hi I have a question here, I have worked out part of the problem but could not figure out how to do the rest:
: A factory bought an equipment for $81000. The equipment has an expected life of 3 yrs with zero salvage value at the end of that time. The expected returns before tax are $77000 at the end of the first and second yrs, $35000 at the end of the third yera. The tax rate is 33% and that is payable on returns of net depreciation calculated as $27000 per year. I.e. after tax returns net of depreciation at the ed of first year is 77000-0.33(77000-27000). HOw to work out the internal rate of return using excel?
: first of all i added up the total return 77000+77000+35000=189000
: assuming the discount rate at 5%,
: the Net Present Value I got was
: 189000(1+0.05)^(-3) - 81000 = 82265.30612
: the I worked out the IRR by hand, but could not fit in the tax or the depreciation part
: fv=p(1+i)^n
: 189000=81000(1+r/100)^3
: 7/3=(1+r/100)^3
: log 7/3 = 3 log(1+r/100)
: 0.122658928 = log (1 +r/100)
: 1.326352403 = 1+ r/100
: 0.326352403 = r/100
: 32.63% =r
: Please help me fit in the tax and depreciation part. thank you.
Got no idea why you've made that so complicated....
here's how I'd do this:
1: What did I invest? $81,000
2: What was that worth 3 years later? $117,000 (revenues of 189,000 less tax of $72,000)
3: $81,000 = $117,000 3 yrars later: what annual rate is required? 13.04%
81000(1+i)^3 = 117000
(1+i)^3 = 117000/81000 = 13/9
1+i = (13/9)^(1/3)
i = (13/9)^(1/3) - 1 = 1.130403814... - 1 = .130403814... = 13.04%
In other words:
you invest $P; Y years later (and only then) you get a clear $F:
who cares what happened year to year: you're only interested
in the end result...
(BM = Bank Manager)
ME: got $100,000 to invest
BM (flashing an even row of generous teeth!): GREAT! and we'll pay you top return!
ME: what rate?
BM: well, we'll pay you 12% compounding hourly for the 1st 2 months, then 11.2% plus bonus of $5 for every
$100 of interest this means for the next 4 months...AND for the last 6 months we'll bend backwards and deposit
your interest each week ,long weekend or not...HA HA, at a rate made up of the average od the first 2 rates
plus a 2% bonus....HOW'S THAT!?
ME: oh boy...just tell me: how much will I have in my account 1 year from now?
BM (seriously punching his Texas Fin. Calc.): 113,211.75 !
.................what's the IRR ????????????????
13.211.75%, right? Who the hell cares HOW it's paid :))