Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Return On Investment

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

What formula would I use to do the following: Show the amount of money I would have if I invested A$ at B% compounded annually for C amount of years. e.g. $100,000 invested at 4% for 8 years, where A=$100,000 and B=4% and C=8 years.

I know there is an easy answer for this in Excel, but I can't seem to find it.

Thanks

View Answers     

Similar Excel Tutorials

FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...
PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...
RATE Function - Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...

Helpful Excel Macros

Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
- This free Excel macro filters a data set to display the bottom 10 percent of the data set in Excel. This is a great mac
Filter Data to Show the Top X Number of Items in Excel - AutoFilter
- This Excel macro filters a data set to display only the top X number of items in that data set in Excel. This means tha
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Similar Topics







I need a formula that spits out a daily compounded interest based on other inputs:

If
a = investment amount
b = current value
c = # of days

Then what is
d, the interest rate, when daily compounding is used?

I already have a spreadsheet set up with the a, b, and c values, whe
a is an amount that never changes b is an amount that I will sometimes explicitly change c is automatically calculated using the DatedIf function I would like a formula that uses the other values to return the interest rate earned based on daily compounding.

I hope this question makes sense. If not, then perhaps the following will help clarify:

I have a mutual fund that has quadrupled in value over 15 years.

If I had invested the same amount in something that paid daily interest over the same number of years, then what interest rate would have returned the same result?

Thank you for your help.


Hello,
I am trying to figure out a formula in Excel how to calculate an investment.
Example: starting at age 58 I invest 100,000 @ 3.5% compounded daily for 7 years I have this formula:
=100000*(1+3.5%/365)^(365*7) = 127760.63
At age 65 I will start getting a monthly payment of 675.00 from the above investment for the next 5 years or 10 years, or more. (assuming I will still earn interest on this investment) What will be the balance amount, if I choose to withdraw the investment (at any time)?
I am looking to create a formula in Excel.
Any help would be appreciated.
FLD


Hello,

I am trying to figure out a formula in Excel how to calculate an investment.

Example: starting at age 58 I invest 100,000 @ 3.5% compounded daily for 7 years I have this formula:
=100000*(1+3.5%/365)^(365*7) = 127760.63

At age 65 I will start getting a monthly payment of 675.00 from the above investment for the next 5 years or 10 years, or more. (assuming I will still earn interest on this investment) What will be the balance amount, if I choose to withdraw the investment (at any time)?

I am looking to create a formula in Excel.

Any help would be appreciated.

FLD


hello again,

i wanted to know how i would calculate the alpha and beta of an investment, for example;

Beta Definition:

A measure of an investment's volatility, relative to an appropriate asset class. For stocks, the asset class is usually taken to be the S&P 500 index.

The formula is:

beta = [ Cov(r, Km) ] / [ StdDev(Km) ]2

where

r is the return rate of the investment;

Km is the return rate of the asset class.

Here is my example;

Return Invested
$34,000.00 $20,000.00 <---Money Invested
$24,000.00
-$2,300.00
-$5,000.00
----------
$50,700.00 <---- Total Return

The formula keys;

r = 253.50% Rate of return ($50,700.00/$20,000.00)

km = 10.00% Return Rate of Asset Class (Industry Average)

stdev = $19,318.97 Standard Deviation =stdev($34,000.00:-$5,000.00)

cov or covar <--- this is the one that stumps me, for covar, you need to columns of data, in my case, the returns of investment, since you need another table to compare data sets, what i did was, in the first column, it represents the amount of money invested each period, total money invested was $20,000.00/4 <--- total number of periods which = $5,000.00 as shown below;

Invested Return
$5,000.00 $34,000.00
$5,000.00 $24,000.00
$5,000.00 -$2,300.00
$5,000.00 -$5,000.00
---------- -----------
$20,000.00 $50,700.00

the formula for covar regarding the above table was =covar(a2:a5,b2:b5) but it equalled 0.000000000. What did i do incorrectly?

Second part, the alpha as defined means the measure of an investments
performance beyond what its beta would predict. How would you go about computing the alpha based on those tables? Once we know the beta through that formula mentioned above, how would you then calculate the alpha?

Look forward to hearing from you. Thank you for reading this...


I can't figure out how to accomplish the following:

Lets say I have an investment and it had an intitial deposit of $100,000 and subsequent annual investments of $20,000, after the sixth year, there would be a total of $200,000 invested, but it hasn't been invested for six years (not the whole 200k). what is the formula/equation to determine the internal length of time the 200k has been invested?

Any help is appreciated.


- Caz


I am sure this is a simple question.

I have a spreadsheet that displays amount invested and net worth vs time (months). I have it set up so that the user of the spreadsheet can input the length of time they can project what their rate of return for "x" amount of months would be. So I have 360 rows (equivalent to 30 years) of data. The user can input something like 120 months (10 years) and see how much he has invested to date and what his expected return would be.

I would like a graph that would vary the x-axis to the limits of 0 to 12 months (for the example) and would be able to change to whatever month that gets specified. Can this be done?


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 have invested $77,000 at an interest rate of 1.05% compounded daily. i want
to create a formula showing the yield at the end of 5 years (by day).



I have a table of monthly investment amounts in A1:B3. The actual data is in a named range called MonthlyAmount (A2:B3).

Date Amount
15/5/2008 100
15/3/2010 50

The table says that from 15/5/2008 (European date format) to 15/2/2010 the amount invested monthly was 100, and from 15/3/2010 the amount invested monthly is 50. The investments occur on the 15th day of each month.

I want a formula which calculates, for a given date, the total invested at that date. Examples:

1/7/2008: 2 months (May & Jun) x 100 = 200.
15/7/2008: 3 months (May, Jun, Jul) x 100 = 300.
1/2/2010: 21 months x 100 = 2100.
15/2/2010: 22 months x 100 = 2200.
1/5/2010: 22 months x 100 + 2 (Mar, Apr) x 50 = 2300.
1/6/2010: 22 months x 100 + 3 (Mar, Apr, May) x 50 = 2350.

I would like the formula to still work if I add another row to MonthlyAmount to change the monthly amount in future. Thanks for any help.


I am trying to write a simple formula to calculate the amount of accrued interest on an investment with multiple in/out dates. In the example below, assume a $100k investment earning 7.50% compounded annually. In addition to the initial investment, assume a capital call (additional deposit) was made a couple years later and then a couple years after that, a distribution was made (assume it goes to pay accrued interest first).

The tricky part - the annual compounding takes place based on the initial investment date, so running FV formulas off of the subsequent investments/distributions is not possible.

I am trying to firm up an investment tracking database for my company's real estate investors, and I'd like to get away from having to build massive spreadsheets that track interest on a daily basis.




Hi ,

I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.

But I want to make it compact , as d one I created is long enough.

A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest

Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?

Please let me know the formula , and help me to make my calculator a bit smart.
Regards


My [lack of] math skills are failing me so thanks in advance for your time. Here is the problem, and the attachment provides a visual and formula template:

Bob invests $75,000 in CompanyX on January 1st 2011.
Tom invests $25,000 in CompanyX on July 1st 2011.

At the end of the year, CompanyX disburses $20,000 in net profit to investors. I am trying to write a formula that determines how much money Bob and Tom would get, respective to the amount they invested as well as when they invested it.

For example, without the time consideration, Bob should get 75% of 20k or $15,000, and Tom should get 25% of 20k or $5,000. However, in reality that is not right...because then Tom would be getting a much higher annual return that Bob gets since he only had his money tied up in CompanyX for half the year. I'd really like the formula to be able to do this for at least three investors (Bob, Tom, and Frank).

Assuming disbursements are only made once per year after the year has ended, my end goal is to use the below three variables to determine the "pro-rata" share of net profit each investor should get (with at least three investors). The variables a
1) Amount Invested 2) Date of Investment 3) Total Net Profit in Dollars at Year's End

Thank you for any ideas on how to tackle this.

-Chris


I am trying to calculate the amount of money that is given to an individual as a percentage of the person's salary, with the percentage varying by the number of years of employment. for example, the amount in cell A1 is the person's years of service (in this example it would be 23.422). The amount in cell a2 is the person's salary, (in this example it would be $35,440) I want the formula to calulate the following:
0 - 9.99 years of employment - 2%
10 - 19.99 years of employment - 3%
20 - 24.99 years of employment - 4%

In this example, the formula should give me $1,417.60 as my answer ($35,440 x 4% for 23.422 years of service) I would like to use this same formula for everyone, and let the formula determine which percentage to use. We can get the formula to work for anyone that has less that 9 years but after that it will not work.

Thanks for your help.


Hi guys,

In my eyes I am facing a big problem.

I have a project where I have to use a loop to calculate the amount of money I receive after X years.

So I set up three cells in Excel.

1. Money at the beginning of the investment horizon
2. Interest Rate
3. Expected Return
4. Result Cell with the number of years, it takes to get the expected return.

I know I could use a logarithm to do it and thats no problem I did it.

But I have to use a Do Loop.

And then it doesnt work.

Here is how I started the loop:

number of years = 0
interest = 0
compound interest = 0

Do
interest = (Money in t=0)*(interest rate)+interest
compound interest = interest*(interest rate) + compound interest
number of years = number of years +1

loop until (Money in t=0)+(interest+compound interest)>= expected money

The problem I have is that the compound interest occurs one year deferred.

WOuld be great if somebody could help me further with this problem.

Thank you guys


Hi guys,

I already posted this before but maybe too early so that not a lot people where already sitting in front of their computers.

Let's try it again.

In my eyes I am facing a big problem.

I have a project where I have to use a loop to calculate the amount of money I receive after X years.

So I set up three cells in Excel.

1. Money at the beginning of the investment horizon
2. Interest Rate
3. Expected Return
4. Result Cell with the number of years, it takes to get the expected return.

I know I could use a logarithm to do it and thats no problem I did it.

But I have to use a Do Loop.

And then it doesnt work.

Here is how I started the loop:

number of years = 0
interest = 0
compound interest = 0

Do
interest = (Money in t=0)*(interest rate)+interest
compound interest = interest*(interest rate) + compound interest
number of years = number of years +1

loop until (Money in t=0)+(interest+compound interest)>= expected money

The problem I have is that the compound interest occurs one year deferred.

WOuld be great if somebody could help me further with this problem.

Thank you guys


The following is a small Calculator i am trying to build that shows the interest of someones money building everyday, but in the output column I cant figure out the rest of the cells.

*(Also can someone double check what i already have for b8,b9, & b10 I'm not sure if they are correct.)

M-CALC


A B 1 AMOUNT IN BANK: $ 10,000.00 2 BANKS INTEREST RATE: 1.90% 3

4 INCRIMENT OUTPUT 5 SECOND:
6 MINUTE:
7 HOUR:
8 DAY: $ 0.52 9 WEEK: $ 3.65 10 MONTH: $ 15.83 11 1/2 YEAR:
12 1 YEAR:
13 2 YEARS:
14 5 YEARS:
15 10 YEARS:
16 20 YEARS:
17 40 YEARS:
18 80 YEARS:

Spreadsheet Formulas Cell Formula B8 =($B$1* (1+$B$2) ^1-B1)/365 B9 =($B$1* (1+$B$2) ^1-B1)/52 B10 =($B$1* (1+$B$2) ^1-B1)/12

Excel tables to the web >> Excel Jeanie HTML 4


Hi All,

I would like to be able to check senarios of the problem.
There is a Capital amount invested with an interest rate of 'x' annually, with a fixed monthly withdrawal - how long would this investment last? - that's the easy part.
Now, if there are periodic amounts (not fixed in amount for frequency) paid in as well as the odd monthly fixed amount left in (not withdrawn for that month), what would the new term be?

Hopefully there are a lot more clever people out there than me.
Many thanks in advance for your assistance.

Regards,
Chubby16

I am not sure how to proceed on this, so any thoughts would be most appreciated. I have a huge list. Col I has a list of ages, Col J has years of service. Col K needs to show how many years they will receive an extra benefit, based upon their age and years of service.

Here's my criteria:

They must be at least 55 with 10 years of service to qualify for this benefit. I have a column AA already to indicate Yes or No whether they meet these minimums. But here's my problem. For those who DO qualify:

They will receive the benefit until age 70 - or whatever comes first:

With 10 years of service - they will receive the extra benefit for 8 years
15 years of service - 9 years
20 years of service - 10 years
25 years of service - 11 years
30 years of service - 12 years
35 years of service - 13 years

So if Col I had 58 and Col J had 25, Col K should show 11
But if Col I had 61 and Col J had 25, then Col K would show 9

I know the answer has got to be simple, but I am just not getting it. Would I convert the years to months, and then use a VLOOKUP somehow? Any ideas how I could go about doing this?


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


I am new to EXCEL and I am trying to write a spreadsheet that;

Present Value $10,000

Interest rate 1% per month(12% annually)
compounded monthly

Monthly withdrawal of $250 end of month

Years 3 years or 36 months of activity

I would like spreadsheet to show future value as well as total interest compounded.
Thank you in advance!




Does anyone know excel formula to calculate the maturity amount if a particular sum is invested every month for a certain period on certain rate of interest e.g if a sum of 100 invested every month in a bank recurring deposit scheme on 9% for 24months what will be the maturity amount after 24 months. Please note sum, rate of interest and period can be different also

The below request is to demonstrate the value of investing in U. S. Savings Series "I" Bonds which are currently earning 5.64% with local and state taxes excluded and federal tax deferred vs a bank savings account that earns the same interest but has taxes applied to the interest annually.
I want to be able to present a graph that represents two ongoing bi-monthly investments of $50 and to show time periods, 1 yr, 5 yr, 10 yr, and 30 yr. one that has monthly interest earnings using 5% annual interest as well as semi-annual compounded interest awarded on the interest earned vs same dollars invested that does not earn compounded interest and is taxed annually of 32%.

If possible also show the difference in the actual dollars earned between the two over the same time periods.

Is this even possible to do or too much to ask the forum at once?


Hi everybody!

I have something that has me completely stumped and am hoping that somebody can help me find an answer.

The scenario is the following:

Coal reserves in a coal mine are up for grabs;
Investors have to pay a certain amount of money to get a certain share in the company that will mine the coal;
The rarred spreadsheet (14.2 KB) can be downloaded from he http://www.gpforum.co.za/example.html (I cannot see how to turn this into a link - sorry!);
The investor will be making the kind of profits indicated in the spreadsheet;
I used both IRr and later XIRR to calculate the return on the investor's money

BUT still do not reach the same answer when I check the returned return with other calculations.

For example, the XIRR calculations show that the investor achieves an annual return of 180% for a specific grade of coal. The project runs for 2 years.

When I take the original investment amount ($X) and use the XIR-determined return rate against it by doing the following ($X * (1+180%))^2), it gives a completely different answer to the actual return.

The rate that XIRR function does not seem to be the right one to use. In this scenario, the initial investment "returns" a series of future cash flows as follows:

The initial investment (out) beginning month 1
An outflow of $y beginning month 2 (expenses to set the operation up - no income earned)
Another outflow ($y) beginning month 2 and month 3
Then, in month 4, the outflows still happen, but the inflows begin.
In month 24, the mining ceases (coal deposits depleted), but money continues to come in until month 27.

The original investment should actually be shown as an "income" in month 27 (because it is to be paid back to the investor as a loan account), but I have not yet done it yet.

Could someone please see what the heck I am not doing right, please?

I gather that XIRR is simply not the right function to use ... Should I use Goal Seek?

Thanks!

Harry


Team,

More of a finance / how to question...


I need to calculate how much money needs to be allocated to an investment account so that some years in the future I'll have the required funds available, given a know investment rate..

E.g. I can invest my money at 5% per year. In 15 years, I need $5,000,000. How much can do I need to divert to the investment fund per month so I yield the required monies.

This would be the opposite of a loan payment.
I can do it on my finance calculator, Just don know how to do it in excel

Any suggestions?

Thanks,

John
In Annapolis.


I have a worksheet with several start dates in date format...mm/dd/yy. I want to find the upcoming key anniversary dates (5 years, 10 years, 15 years, 20 years, 25 years, 30 years, 35 years, 40 years, 45 years) of all employees so recognition can be given. I would like the column/field to be highlighted to show me the employees who have reached the 5, 10, 15, 20, 25 year mark etc.. I am using Excel 2007..any experts out there who could help me out with a formula?