Posted by Subhotosh Khan on September 20, 2002 at 15:45:08:
In Reply to: Re: internatl rate of return (IRR) posted by Denis Borris on September 20, 2002 at 15:18:17:
: : 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 :))
*****************************************
Shouldn't we have:
81000(1+i)^3 = .67*(77000*(1+i)^2+77000*(1+i)+35000)
If that is true then I get 1+i = 1.2989
or IIR = 29.89%