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!

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!

- Financial Calculation: Return on Investment
- Excel formula for (ROI) Return on Investment
- If i input a number
- Q re: calculation of alpha and beta on investment returns
- Rate of return on investment per annum
- Investment question
- How to calculate return on a open-ended mutual fund?
- Determine Present Value From Future Value
- Calculate Monthly & Annual Returns of Security Or Index
- Calculating geometric mean
- How to Calculate Return on Investment (ROI) in years
- Average annual return
- Annualized return calculation for the purpose of portfolio performance evaluation
- Excel Annualized Return Formula
- The sum of multiple returns on a vlookup
- How to calculate total interest on 12 month loan with early payments
- Excel - tuition savings question
- Between 2 cells: if you type on any cell a value to be returned on the second
- Multiple variables effect on two result cells
- Using Randbetween to Project Annual Sales on Monthly Basis
- Excel Annualized Return Formula
- Annualized return calculation for the purpose of portfolio performance evaluation
- Average annual return
- Matching values & returning on the same row

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.

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

:/

-_-

Thanks in advance.

jim

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?

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

investor can invest at different points of time at different NAV and offload

the investment at a certain NAV.

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

Code:

Thank you.

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

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

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

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

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

>

>

>

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

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.

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)

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.

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

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

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

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