SOLVED

sum quotient

%3CLINGO-SUB%20id%3D%22lingo-sub-239723%22%20slang%3D%22en-US%22%3Esum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239723%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Ei'm%20not%20an%20expert%20at%20excel%20so%20my%20question%20might%20seem%20dumb%20but%20I%20have%20the%20following%20problem%3A%20I%20have%20one%20column%20stating%20the%20period%20(continuous%20from%201-28)%26nbsp%3B%20i%20have%20another%20column%20stating%20total%20cash%20flows%20(armortization%20plus%20interes)%20for%20the%20given%20period%20and%20I%20have%20the%20initial%20costs%20which%20are%20100.%20Now%20I%20want%20to%20calculate%20the%20IRR%20(internal%20rate%20of%20return)%20which%20calculates%20as%20follows%3A%3CBR%20%2F%3Esum%20of%5BC_t%2F(1%2Br)%5Et%5D-C_0%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20C_t%20is%20the%20cash%20flow%20for%20the%20given%20period%20t%20(different%20in%20every%20period)(flujo%20total).%3C%2FP%3E%3CP%3EC_0%20are%20the%20initial%20costs%3C%2FP%3E%3CP%3Eand%20t%20is%20the%20period%20(cupon)%3C%2FP%3E%3CP%3Ethis%20equation%20has%20to%20be%20set%20equal%20to%200%20and%20i%20have%20to%20solve%20for%20r%20which%20will%20be%20my%20IRR.%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20this%20in%20Excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-239723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239882%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239882%22%20slang%3D%22en-US%22%3E%3CP%3Ewelcome%20-%20glad%20to%20help.%3C%2FP%3E%3CP%3Ethanks%20to%20Mr.%20Chan%2C%20too...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239841%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239841%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20thanks%20a%20lot%20for%20all%20the%20help!%20XIRR%20is%20the%20solution%20to%20my%20problem!%20(only%20the%20dates%20are%20a%20little%20messed%20up%20but%20I%20will%20be%20able%20to%20solve%20on%20the%20basis%20of%20XIRR!%20So%20thanks%20a%20lot%20for%20all%20your%20suggestions!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239746%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239746%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Chan%3C%2FP%3E%3CP%3Ethank%20you%20for%20helping..%3C%2FP%3E%3CP%3EI%20think%20what%20Mr.%20Behler%20wanted%20is%20to%20determine%20first%20what%20the%20value%20of%20r%20is%20--%20this%20is%20what's%20jolting%20my%20brain!%3C%2FP%3E%3CP%3Eanyway%20I%20hope%20He%20can%20see%20something%20out%20of%20this...%3C%2FP%3E%3CP%3Emany%20thanks%20and%20good%20health.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239744%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239744%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20invited%20by%20Mr%20Kim%20for%20help.%20However%2C%20I%20am%20not%20so%20familiar%20with%20Financial%20Mathematics.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20column%20to%20create%20the%20quotients%20(C%2F(1%2Br)%5Et)%2C%20where%20r%20is%20indicated%20in%20L1.%20Changing%20the%20value%20in%20L1%20will%20give%20a%20different%20sum%20in%20L4.%20So%2C%20the%20user%20can%20guess%20the%20value%20of%20r.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20function%20called%20%22XIRR%22%20which%20seems%20to%20do%20this%20type%20of%20problem.%20But%2C%20I%20do%20not%20understand%20the%20setting.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239739%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239739%22%20slang%3D%22en-US%22%3Emy%20apologies%20-%20this%20turns%20out%20to%20be%20very%20complex%20for%20me.....%3CBR%20%2F%3Esolving%20for%20I2%20is%20-%20there%20could%20be%20a%20circular%20reference%20in%20this..%3CBR%20%2F%3Emaybe%20other%20people%20can%20solve%20this..%3CBR%20%2F%3Egood%20luck%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239736%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239736%22%20slang%3D%22en-US%22%3E%3CP%3Ewhat%20i%20want%20to%20do%20is%3A%3C%2FP%3E%3CP%3E(E2%2F(1%2BI2)%5EB2%2BE3%2F(1%2BI2)%5EB3%2BE4%2F(1%2BI2)%5EB4%2BE5%2F(1%2BI2)%5EB5%2BE6%2F(1%2BI2)%5EB6%2BE7%2F(1%2BI2)%5EB7%2BE8%2F(1%2BI2)%5EB8%2BE9%2F(1%2BI2)%5EB9%2BE10%2F(1%2BI2)%5EB10%2BE11%2F(1%2BI2)%5EB11%2BE12%2F(1%2BI2)%5EB12%2BE13%2F(1%2BI2)%5EB13%2BE14%2F(1%2BI2)%5EB14%2BE15%2F(1%2BI2)%5EB15%2BE16%2F(1%2BI2)%5EB16%2BE17%2F(1%2BI2)%5EB17%2BE18%2F(1%2BI2)%5EB18%2BE19%2F(1%2BI2)%5EB19%2BE20%2F(1%2BI2)%5EB20%2BE21%2F(1%2BI2)%5EB21%2BE22%2F(1%2BI2)%5EB22%2BE23%2F(1%2BI2)%5EB23%2BE24%2F(1%2BI2)%5EB24%2BE25%2F(1%2BI2)%5EB25%2BE26%2F(1%2BI2)%5EB26%2BE27%2F(1%2BI2)%5EB27%2BE28%2F(1%2BI2)%5EB28%2BE29%2F(1%2BI2)%5EB29)-100%3C%2FP%3E%3CP%3Eset%20this%20equal%20to%200%20and%20replace%20I2%20by%20the%20value%20which%20gives%20me%200%20for%20the%20equation.%20(whats%20an%20easy%20way%20to%20write%20it%3F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239731%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239731%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20want%20to%20do%20the%20following%20with%20my%20numbers%3A%3C%2FP%3E%3CP%3E7.776%2F(1%2Br)%5E1%2B7.898%2F(1%2Br)%5E2%2B8.054%2F(1%2Br)%5E3%2B%E2%80%A6%2B3.248%2F(1%2Br)%5E28%20-%20100%3C%2FP%3E%3CP%3Eset%20equal%20to%200%20and%20solve%20for%20r%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%20i%20could%20imagine%20putting%20this%3A%3C%2FP%3E%3CP%3E%3D(E2%2F(1%2BI2)%5EB2%2BE3%2F(1%2BI2)%5EB3%2BE4%2F(1%2BI2)%5EB4%20and%20so%20on%20subtract%20by%20100%20and%20then%20using%20the%20solver%20chaning%20the%200.2%20(which%20are%20a%20random%20number%20now)%3C%2FP%3E%3CP%3Ebut%20im%20not%20sure..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239730%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239730%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Mr.%20Kim%2C%3C%2FP%3E%3CP%3EFirst%20of%20all%20thanks%20for%20the%20reply.%20However%20this%20is%20not%20the%20solution%20to%20my%20problem.%20Maybe%20i%20didn't%20express%20it%20well.%20What%20im%20trying%20to%20do%20is%20getting%20the%20present%20value%20of%20each%20periods%20cash%20flow%20with%20an%20unknown%20discount%20rate%20(C_t%2F(1%2Br)%5Et)%2C%20add%20them%20all%20together%20(all%20the%20cash%20flows%20for%20each%20period)%2C%20subtract%20it%20by%20the%20initial%20costs%20and%20then%20solve%20for%20the%20discount%20rate.%20In%20the%20sheet%20you%20send%20me%20some%20numbers%20got%20mixed%20up.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239726%22%20slang%3D%22en-US%22%3ERe%3A%20sum%20quotient%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239726%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Behler%3C%2FP%3E%3CP%3Eplease%20see%20attached%20file%20if%20this%20is%20what%20you%20need.%3C%2FP%3E%3CP%3Edo%20not%20test%20this%20directly%20on%20your%20file..%20try%20it%20first%20in%20the%20test%20sample.%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

i'm not an expert at excel so my question might seem dumb but I have the following problem: I have one column stating the period (continuous from 1-28)  i have another column stating total cash flows (armortization plus interes) for the given period and I have the initial costs which are 100. Now I want to calculate the IRR (internal rate of return) which calculates as follows:
sum of[C_t/(1+r)^t]-C_0

 

where C_t is the cash flow for the given period t (different in every period)(flujo total).

C_0 are the initial costs

and t is the period (cupon)

this equation has to be set equal to 0 and i have to solve for r which will be my IRR.

How do I do this in Excel?

9 Replies
Highlighted

Mr. Behler

please see attached file if this is what you need.

do not test this directly on your file.. try it first in the test sample.

Hope this helps.

thanks 

Highlighted

Dear Mr. Kim,

First of all thanks for the reply. However this is not the solution to my problem. Maybe i didn't express it well. What im trying to do is getting the present value of each periods cash flow with an unknown discount rate (C_t/(1+r)^t), add them all together (all the cash flows for each period), subtract it by the initial costs and then solve for the discount rate. In the sheet you send me some numbers got mixed up.

Highlighted

i want to do the following with my numbers:

7.776/(1+r)^1+7.898/(1+r)^2+8.054/(1+r)^3+…+3.248/(1+r)^28 - 100

set equal to 0 and solve for r

 

 

for example i could imagine putting this:

=(E2/(1+I2)^B2+E3/(1+I2)^B3+E4/(1+I2)^B4 and so on subtract by 100 and then using the solver chaning the 0.2 (which are a random number now)

but im not sure..

Highlighted

what i want to do is:

(E2/(1+I2)^B2+E3/(1+I2)^B3+E4/(1+I2)^B4+E5/(1+I2)^B5+E6/(1+I2)^B6+E7/(1+I2)^B7+E8/(1+I2)^B8+E9/(1+I2)^B9+E10/(1+I2)^B10+E11/(1+I2)^B11+E12/(1+I2)^B12+E13/(1+I2)^B13+E14/(1+I2)^B14+E15/(1+I2)^B15+E16/(1+I2)^B16+E17/(1+I2)^B17+E18/(1+I2)^B18+E19/(1+I2)^B19+E20/(1+I2)^B20+E21/(1+I2)^B21+E22/(1+I2)^B22+E23/(1+I2)^B23+E24/(1+I2)^B24+E25/(1+I2)^B25+E26/(1+I2)^B26+E27/(1+I2)^B27+E28/(1+I2)^B28+E29/(1+I2)^B29)-100

set this equal to 0 and replace I2 by the value which gives me 0 for the equation. (whats an easy way to write it?)

Highlighted
my apologies - this turns out to be very complex for me.....
solving for I2 is - there could be a circular reference in this..
maybe other people can solve this..
good luck
Highlighted
Solution

I was invited by Mr Kim for help. However, I am not so familiar with Financial Mathematics. 

 

I created a column to create the quotients (C/(1+r)^t), where r is indicated in L1. Changing the value in L1 will give a different sum in L4. So, the user can guess the value of r.

 

There is a function called "XIRR" which seems to do this type of problem. But, I do not understand the setting. 

Highlighted

Mr. Chan

thank you for helping..

I think what Mr. Behler wanted is to determine first what the value of r is -- this is what's jolting my brain!

anyway I hope He can see something out of this...

many thanks and good health.

 

Highlighted

Hey thanks a lot for all the help! XIRR is the solution to my problem! (only the dates are a little messed up but I will be able to solve on the basis of XIRR! So thanks a lot for all your suggestions!

Highlighted

welcome - glad to help.

thanks to Mr. Chan, too...