Free Microsoft Excel 2013 Quick Reference

# annualized return on investment

Initial deposit: July 1 2005 – \$254,764
Withdrew – \$5,000 per mo starting july 2005 through june 2008
Balance in account June 30, 2008 128,152
What was the annualized return on investment?

I am trying to use the xirr function doing initial investment - 5,000 for each month as the values however I keep getting an error. How do I set up the table to get the XIRR function to work? Thanks!

## Related Results

### Financial Calculation: Return on Investment

Looking for a method to calculate the ROI (Return on Investment) directly
from First Cost, Annual Return and Number of Years. The built-in spreadsheet
function IRR does this, but only for a fixed array of data entry. For
example, if you know the project service life will be seven years, you could
put the First Cost into cell A1, then the annual returns in cells A2:A8.
Then the formula to calculate ROI in cell A10 would be "=IRR(A1:A8,0.1)" and
it would automatically calculate it.

But, I want to be able to use this for projects of varying service life
periods. Rather than have a string of annual cash flows set into a fixed
array of cells, there will be just the 3 parameters for cost, return, years.
To do this manually using Compound Interest tables calculate the annuity
ratio, then flip through the tables until you find a corresponding value at
the specified number of years. Looking for an elegant way to essentially do
that using formulae or user-defined function.

### Excel formula for (ROI) Return on Investment

I need an excel formula for ROI (Return on Investment) for my marketing business.

### If i input a number

I have a "financial" spreadsheet. I am not able to describe it well but, to put it vaguely, several cells display errors when I input an integer less then 10 into a precedent cell. If anyone would like to see the worksheet let me know. Thank you!

If i start at 25 years it works ok

Term 25
Repayment Year 25

IRR to Maturity (repayment/sale at par) 43.33%
Annual Return on Investment -0.911%

I type in 10 years it works ok

Term 10
Repayment Year 10

IRR to Maturity (repayment/sale at par) 35.65%
Annual Return on Investment 0.273%

If I go lower than 10...

Term 9
Repayment Year 9

IRR to Maturity (repayment/sale at par) #num
Annual Return on Investment #value

:/

-_-

### Q re: calculation of alpha and beta on investment returns

I need to calculate standard deviation (which I can handle) plus the alpha and beta of the returns on an investment portfolio. I have the actual returns for both the subject portfolio and the benchmark. Any thoughts on how to handle this?

jim

### Rate of return on investment per annum

Hi,

I have gone thru the forum discusiions on simiar topics, but couldnt
understand much. appreciate your help with this query

1) i invest \$100000 for 3 years one month today with reinvesment of interest
earned quaterly at the rate of 7.00% per annum. What wil be ultimate amount
at the end of the tenure?

2) I invest \$100000 today in a scheme which will give me 250000 after 2
years. what is the rate of return here?

### Investment question

Hi,
I'm trying to calculate my return on investment.
2/5/2008 invested \$2700
9/30/2008 investment is worth \$4200
How much interest did I earn if compounded annually? I tried the intrate formula but couldn't get it to work. Any replies would be greatly appreciated!
Joanne

### How to calculate return on a open-ended mutual fund?

I want to know how to calculate return on an open-ended mutual fund where
investor can invest at different points of time at different NAV and offload
the investment at a certain NAV.

### Determine Present Value From Future Value

Hi!

Next problem!

I have the following:

I pay \$2740.68 (paid annually in advance) into an investment;
The \$2740.68 increases by 5% at the beginning of each new year;
The maturity date of this investment is exactly 20 years from now;
The investment has already been going for 10 years (from that you can deduce (if you have to), that the original premium was less than the current \$2740.68);
The current value of the investment is unknown;
To illustrate a future maturity value, all investments are assumed to earn 10% growth(interest);
The illustrative future value supplied by the insurance company for this investment (over the full 30 year term while assuming an annual 5% increase in premium and 10% returns on invested premiums) amounts to \$616,850.

If I am to mature my investment 10 years before the actual maturity date, what should the investment be worth at the supplied returns and premiums?

In the attached spreadsheet I explained, in a step by step fashion, what I did to get an answer to my problem. The answer I got is \$178,554.87.

Does it look as though I reached the right answer?

Hope you can help - or tell me that I succeeded in doing something correct!

Best wishes

Harry

### Calculate Monthly & Annual Returns of Security Or Index

I feel pretty dumb asking a basic math question but I couldn't find the answer anywhere. I would like to calculate the monthly & annual returns of a security or index. When trying to calculate various indexes to insure my math was correct, my numbers never match that of yahoo or bloomberg's. If you could just start me down the path, I would appreciate it. mr brain dead

### Calculating geometric mean

I have the following annual returns on an investment and would like to calculate the compund annual return. How can I do this without actually doing the multiplication to find the ending value and then using (Future Value/present value)^(1/t)-1?

Code:
```Thank you.

```

### How to Calculate Return on Investment (ROI) in years

Dear Message Board -

Thanks in advance for help with this.

I have a list of gains and costs across 4 years, and need to calculate ROI IN YEARS for the investment.

I can get to 5% ROI, but I need something like 1.25 Years too.

Help.
Thanks
Evelyn

### Average annual return

Does anyone know if there is a formula function like IRR (a,b) for
average annual return if I have string of returns that are quarterly-
I have been calculating this in a manual formule but I was wondering if
there was a short cut formula

--
rathersurfPosted from http://www.pcreview.co.uk/ newsgroup access

### Annualized return calculation for the purpose of portfolio performance evaluation

I'm trying to evaluate the performance of a couple of stock portfolios
and I need to calculate Sharpe,Treynor Ratios, Information Ratio and
also Jensen Measure.
I have a monthly closing prices for all the stocks in a portfolio for
48 months.
My problem is that i'm not sure which rate of return to use in the
ratios..i figured that the annualized rate of return for the 48 months
would be better than just average annual rate of return. How should I
compute the annualized rate of return from the data i have?
I found an article with an example (of what i think I should do) of
calculation of annualized return with the mothly data at
http://www.russell.com/ca/Investor_S..._of_Return.asp
But I still don't have a formula and I'm not really sure whether it's a
right way.
Also when I calculate those compound indicators(Sharpe) do I have to
use annualized standard deviaton or is it ok to use just a standard
deviaton of the monthly returns?
Too many questions I know, but I'm kinda lost.
I guess I might as well stick with the average annual rate which would
make things less complicated

### Excel Annualized Return Formula

I am looking for a formula that will calculate annualized return.
Specifically, I have the annual returns for the S&P 500 from 1976 through
2005. I would like to be able to calculate the annualized (compounded or CAGR)
rate of return over various time frames. There is no formula listed under
tools for Annualized return or CAGR in excel. Geo metric mean (GEOMEAN) is
the closest option but it will not take negative numbers. Can anybody help?

thanks,

evanolsen[at]msn[dot]com

### The sum of multiple returns on a vlookup

I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Alibo

### How to calculate total interest on 12 month loan with early payments

You want XIRR (it's in the Analysis Toolpak -- if you haven't loaded it,

Assume A1:A7 has your seven cash flows (-25 plus your 6 repayments)
Assume B1:B7 has your seven dates (eg, 1/15/05, 7/15/05, 8/15/05, etc)

Result = XIRR(a1:a7,b1:b7)

--
Regards,
Fred

"KG" > wrote in message
news:E40Dd.721967\$%k.504818@pd7tw2no...
>I would like to calculate the total interest earned on a 12 month loan that
>gets paid back in 6 equal, monthly payments in months 7-12. Each of the 6
>equal, monthly payments will include 1/6 of the return on investment. Here
>is a scenario:
>
> Capital Invetsted = \$25,000
> Return on Investment (20%) = \$5,000
> Total Repayment = \$30,000
> 6 Equal Payments = \$5,000 (\$30,000 / 6)
>
> Payment schedule:
> Month
> 1 \$0
> 2 \$0
> 3 \$0
> 4 \$0
> 5 \$0
> 6 \$0
> 7 \$5,000
> 8 \$5,000
> 9 \$5,000
> 10 \$5,000
> 11 \$5,000
> 12 \$5,000
>
>
>

### Excel - tuition savings question

Folks,
Need some help here. This is the problem - need to solve in Excel.
John will enter college 6 years from now, and spend 4 years in
college. The current annual cost of college education is \$25,000 and
is expected to rise 5% each year. Thus, the first college payment is
due 6 years from now and the last payment is due 9 years from now.
Dad would like to make 10 annual investments beginning now and ending
9 years from now such that the total value of his investments and
returns will exactly cover the cost of college. He would like to
determine his first investment, which he plans to increase in
subsequent years by 6% each year. He expects to earn 8% annual return
on his investments.
a) Need to develop a table that shows investments, returns, tuition
expenes and account balances for each year
b) What is the first year's investment (the answer is \$8797.55).

I would appreciate any guidance on this.

Thanks,
Kevin

### Between 2 cells: if you type on any cell a value to be returned on the second

Is that possible?

What i want to do is between 2 cells, for example A1, B1, when i type a value on A1 a value to be returned on B1 and when I type on B1 a value to be returned on A1.

Something like a EURO DOLLAR currency converter but for different reasons.

Is there a way to do it with Macros or VBA? (If not with basic excel tools)
Will it be easier to use 2 textboxes for that reason? If yes what kind of code i will need?
I not very good with VBA, i would appreciate any help for the specific code

Thanks in advance for any replies.

### Multiple variables effect on two result cells

So I was wondering if there was a way to automatically see the effect of changing two user entered variable cells on two other cells.

I am creating a spreadsheet for calculating Stock payouts, and I want to compare dollar amount return on investment (After costs have been covered) with the total % increase in Stock value. (Obviouslly wishing to find the highest \$ increase with the lowest % increase). I need to calculate this based upon user entered variables of Amount of stock to keep invested after I cover, and the desired rate of return.

I'm stumped here. Any help would be great. I attempted two variable data tables with the difference between price and percentage as my result cell, however I wasnt thinking, and that gave me results like (Leave 90% Invested and expect a 6000% return)

### Using Randbetween to Project Annual Sales on Monthly Basis

Hi,

I am in need of help using Randbetween (or any other method) to randomly project annual sales on a monthly basis. Example: A total of 20 sales are projected in 2013 that I need to randomly spread by month with a monthly minimum of 0 and maximum of 2. Duplicates are okay but the total has to equal 20.

Any ideas would be greatly appreciated. Thanks in advance.

### Excel Annualized Return Formula

I am looking for a formula that will calculate annualized return.
Specifically, I have the annual returns for the S&P 500 from 1976 through
2005. I would like to be able to calculate the annualized (compounded or CAGR)
rate of return over various time frames. There is no formula listed under
tools for Annualized return or CAGR in excel. Geo metric mean (GEOMEAN) is
the closest option but it will not take negative numbers. Can anybody help?

thanks,

evanolsen[at]msn[dot]com

### Annualized return calculation for the purpose of portfolio performance evaluation

I'm trying to evaluate the performance of a couple of stock portfolios
and I need to calculate Sharpe,Treynor Ratios, Information Ratio and
also Jensen Measure.
I have a monthly closing prices for all the stocks in a portfolio for
48 months.
My problem is that i'm not sure which rate of return to use in the
ratios..i figured that the annualized rate of return for the 48 months
would be better than just average annual rate of return. How should I
compute the annualized rate of return from the data i have?
I found an article with an example (of what i think I should do) of
calculation of annualized return with the mothly data at
http://www.russell.com/ca/Investor_S..._of_Return.asp
But I still don't have a formula and I'm not really sure whether it's a
right way.
Also when I calculate those compound indicators(Sharpe) do I have to
use annualized standard deviaton or is it ok to use just a standard
deviaton of the monthly returns?
Too many questions I know, but I'm kinda lost.
I guess I might as well stick with the average annual rate which would
make things less complicated

### Average annual return

Does anyone know if there is a formula function like IRR (a,b) for
average annual return if I have string of returns that are quarterly-
I have been calculating this in a manual formule but I was wondering if
there was a short cut formula

--
rathersurfPosted from http://www.pcreview.co.uk/ newsgroup access

### Matching values & returning on the same row

I have a workbook with a worksheet of data. I tried to use a formula to
accomplish my goals, but it's not working exactly the way I wanted. I was
wondering if there is a macro that could do the work a little more
efficiently and user friendly.

On Worksheet1 ("Sheet1") I have data in A1:AI400. Columns A:Q is considered
one set of data and Columns S:AI is my second set of data. Column R is empty
as a spacer. There are dates in columns A & S. There is corresponding data
in each row to go with the date. See below:

"Sheet1"
Col A B C D : Q R (empty) S T U V :
AI
01Jan04 1 3 2 4 04Jan04 2 3 2 1
02Jan04 2 2 2 3 05Jan04 1 2 3 4
03Jan04 4 3 2 1 06Jan04 3 1 2
1
04Jan04 3 1 1 2 07Jan04 2 4 1 2

What I want to do is on "Sheet3" (I'm using Sheet2 for something else) look
at Sheet1 and each date in Column A, compare it to Column S. If it finds a
match in Column S, write the date and corresponding information for that date
on Sheet3.

So based on my example above and doing what I want, I should see on Row 3 is:

"Sheet3" (There are headers in Rows 1 & 2, the returned data should start
on Row 3)
Col A B C D : Q R (empty) S T U V :
AI
1...................................................................................
2...................................................................................
04Jan04 3 1 1 2 04Jan04 2 3 2 1

It is important that the dates that match return on the same row. I'm not
even sure what kind of macro would do this so I'm stumped at where to start.
Any input or code would be greatly appreciated.

Thanks,

Sharon

p.s. Here's the formula I was using, but it doesn't return the dates on the
same rows in Sheet3 and it still returns all the data, not just the matching
ones. Based on my example above, the following formula would return my first
set of data on row 4 and my second set of data on row 1.

I have headers in rows 2 & 3 on Sheet3.

"Sheet3"
Col A B C D : Q R (empty) S T U V :
AI
1...................................................................................
2...................................................................................
1 3 2 4 04Jan04 2 3
2 1
2 2 2 3
1 2 3 4
4 3 2 1
3 1 2 1
04Jan04 3 1 1 2 2 4
1 2

For example: the formula in cell A3 is:
=IF(ISNA(VLOOKUP(Sheet1!A1,Sheet1!\$S\$1:\$AI\$400,1,FALSE)),"",VLOOKUP(Sheet1!A1,Sheet1!\$S\$1:\$AI\$400,1, FALSE))