Posted by Subhotosh Khan on September 20, 2002 at 16:42:00:
In Reply to: Re: internatl rate of return (IRR) posted by Subhotosh Khan on September 20, 2002 at 15:45:08:
: : : 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:
: 81(1+i)^3 = 60.5*(1+i)^2+60.5*(1+i)+32.36
: If that is true then I get 1+i = 1.4513
: or IIR = 45.13%
Post a Followup