Excel Statistics 35.2: Geometric Average V Arithmetic Mean - Finance Example

I've got a simple rate of return question but I think the answer may not be as simple.

25 years ago I invested $5,000 into a privately owed company. The company just sold I got a check for $85,000 (I received no return, no dividends, nothing during the entire 25 years)....my account was always worth $5,000.

In calculating the return I took the $85,000 - $5,000 initial investment = $80,000 profit / 25 years = $3,200 per year / $5,000 = 64% annual return.....I've been told this is the "arithmetic" way to calculate things AND its incorrect.

I've been told if I used the geometric way to calculate the return its more like 12%.

I totally understand compounded returns BUT in the above case there wasn't a compounded return......it was always worth $5,000 until the 25th year and in the 25th is when the big return was realized.

I tried to explain this to a friend (not really a friend) and he just could not believe the return was 64%...he kept saying my return was only 12%.

I even tried presenting it another way:

Say, I lent $5,000 to a company at 64% annual interest (simple interest and no compounding). This would return: $5,000 x 64% = $3,200 x 25 years = $80,000 in profits and he still said my return was only 12%.

Since my background is real estate my method of calculation seems correct BUT then again it may be wrong.

I was wondering if anyone could shed some light on this matter.

Thanks.

Bruce

Hi,

I want to find the average performance for a portfolio, which include shares
with a positive and negative performance. How can I calculate the average
performance, when I have negative numbers? Geomean does not work, and simple
average does not produce a theoretical correct number.

Please help to find a single formula to calculate negative and positive stream of returns. The problem is that =geomean formula does not work with negative numbers.
Here's an example on how to calculate geometric mean with 5 numbers:
Stream of numbers: 0.5, -1.4, -6.5, 0.3, -2.7

First step: I have to add 1 to all numbers (they are positive now)
Second step: =Product (multiply all numbers)
Third step (result): find 5th root(there are 5 numbers) of their product (or raise it to 1/5 power)

I can substitute steps 2 & 3 with a formula =geomean() and the answer will be correct except it won't be negative...but i think there should be another solution, a single formula.
Any idea? Thank you so much!

So, I tried seeking help in my very first thread, but something tells me the title bored potential helpers. Here's the original thread:

http://www.mrexcel.com/forum/showthread.php?t=555178

And here's a summary of the problem:

I'm using an excel spreadsheet to help structure interest rate derivatives. I have run into an impenetrable problem. If you don't want to read the first thread, picture this:

Column A: represents the PPMT for a particular loan. These numbers will all be different and could be $0 if there is are X periods of interest only.

Column B: needs to be the average of every 12 corresponding cells in Column A (B1 maps to A1, etc), but if there are X periods of interest only, then the result needs to be $0 and average needs to begin at AX and map to BX, etc.

Right now, I'm using a MOD function to try and get the cells to average every 12 cells after all non-zero numbers but I can't make it work.

All help is appreciated. Images of the spreadsheet would probably help, but I can't figure out how to load/attach them.

Please help me to approach this question with excel:

Which of the term of the sequence 3/16, 3/8, 3/4, ..., 96 is the last given term?

I work in the custom cabinetry and millwork business and would like to know how involved or difficult the following Excel task might be:

I've developed a spreadsheet which calculates part sizes for wood cabinet doors. (Inputs are the door heights and widths, together with a family of parameters specific to the particular door style. These values plug into formulas which then generate the part sizes.) My spreadsheet is working fine so far, but I would like to incorporate diagrams into the output. As a first step, a typical diagram would be a simple line drawing of a cabinet door, which is basically just a set of concentric rectangles that I would want to be sized and scaled to be an accurate proportional representation of each door. To be clear, no matter what the actual size of the door is, each diagram should be scaled to fill an allotted space on the printed page. The diagram would need to be dimensioned, with the dimensions themselves coming from the output of the spreadsheet. How would you recommend I accomplish this? If it's helpful, I'm using AutoCAD also.

Short of actually having excel draw the diagram to perfect proportional scale, I wonder if a sensible work-around might be that I create a large number of AutoCAD-drawn dummy door diagrams which cover a wide range of possible door sizes and shapes. Excel could choose the dummy diagram which comes closest to matching the proportions and part widths for a given door. It wouldn't be perfect, but given enough of a selection (several hundred dummy diagrams maybe), it might be close enough for my purposes. I'm not sure if this is a simpler solution? Even if it is, I don't really have any idea how to dimension each diagram automatically. The dimensioning should look like a dimension you would find on any architectural drawing, with little lines "snapping" to the end points, a long dimension line connecting them, with the value written in the middle.

Thanks -- hope this all makes sense!

I want to purchase a new laptop but do not know if I need a 64 bit processor in order to make use of the 64 bit long integer capability of Excel 2007. Will the Intel Core 2 32 bit processors enable the 64 bit long integer arithmetic function of Excel 2007? Thank You.

I need to write an Excel function that returns best price of a government bond.

The actual handwritten formula is quite complicated, impossible to display
here, and the input values for this formula are-----------------
Accrued days, Number of coupon period days, Days from settlement to next
coupon date, coupon periods per year, number coupon periods between
settlement & redemption, annual yield, discount rate.

I'm trying to set up a formula that will determine the last amount of cash flow in a stream of payments that is required to reach a certain return. For example, if I have these cash flows YR 0 -50,000,000, YR 1 4,922,955, YR 2 7,086,093, YR 3 8,486,159, I want a formula that will determine how much cash flow I need in YR 4 to achieve a 10% IRR. Any suggestions?

hey guys, so i am trying to import

HTML Code:

http://finance.yahoo.com/q/hp?s=MSFT

that type of information into excel via macro. i can do it by importing it via webquery but the problem is that i need more than one page (go all the way down and there is a NEXT button to get information that is older)

so i am trying to import not just one page, but lets say import 5 or 6 (which will give me a years data on a stock vs. the 3 months)

and then down the road, id like to get rid of the line that shows the dividend payout, but that isn't too important right now.

is this possible what i am asking?