Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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!


Post your answer or comment

comments powered by Disqus
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.

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

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%
Spread over Treasuries 3,914
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%
Spread over Treasuries 3,146
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
Spread over Treasuries #num
Annual Return on Investment #value

:/

-_-

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?

Thanks in advance.

jim

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?

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

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.

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

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

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.

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

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

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
Thanx for your help

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

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

You want XIRR (it's in the Analysis Toolpak -- if you haven't loaded it,
goto Tools>Addins)

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
Please reply to newsgroup, not e-mail

"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
>
>
>

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

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.

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)

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.

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

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
Thanx for your help

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

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
Header Row
1...................................................................................
Header Row
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
Header Row
1...................................................................................
Header Row
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))


No luck finding an answer? You could always try Google.