Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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


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


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


Sub: Retirement Investment Formula in Excel

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


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


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?


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


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.


Q.1
If you wish to haved Rs. 2,000,000 after 30 years from now, what will need to invest this year if you earn 5% interest compounded semi annully?

Q.2
Ali decides to save Rs. 50,000 per year so that he can make a down payment on a house in 6 years. If he makes the payments at the end of each month, assume an interest of 6% compounded monthly, how much will he have accumulated at the end of 6 years?

Q.3
f you want to withdraw Rs. 25,000 at the end of each quarter for the next 7 years then what amount must you invest today at 5% compounded quarterly?
thanks
marshad


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.




I've struggled with this concept for quite awhile and have never been able to figure out an easier way to calculate this.

EXAMPLE:

Investor capital contributions; earning 8% preferred return (compounded annually)

1/1/08 - Investor invests $1,000
3/1/08 - Same investor invests an additional $500 ($1,500 total)
9/1/08 - same investor invests an additional $500 ($2,000 total)
3/1/09 - Investor receives a distribution of $250 ($1,750 outstanding)

Obviously if the investor had only made 1 investment, I would be able to determine his/her outstanding accrued preferred return with a simple FV formula. BUT, since this investor has made multiple investments and has received one distribution, I can't seem to get my mind around how better to calculate this than by what I currently do:


As you can see, I have a column that determines when to compound (12mo after the initial investment) and the "pref basis" column is the amount upon which the preferred return is calculated (the accrued pref doesn't get added to the invested capital - only used for taking into account the compounding of the pref). Then I just LOOKUP the outstanding amount - the problem lies when I am calculating a LONG investment horizon or MANY MANY concurrent investments. The file just gets to be too big.

So, my challenge is to figure out some way to setup a smaller group of cells (with just investment/distribution dates, amount, pref rate) that will be able to spit out the accrued preffered return for any specified date.

is this doable?


Work four summers, starting at age 16

Save the income in a Roth IRA account

Invest it in a simple, low-cost equity portfolio

Simmer slowly for 47 years

Serve ungarnished (and untaxed) at age 67

If your money is invested in common stocks and you achieve the average
compound annual rate on large-capitalization U.S. stocks, 10.7%, your
account will grow to $9,378 at the end of the fourth year. You will be
20 years old. Invested in the same way, with no additional savings, the
account will grow to:
$25,917 by the time you are 30

$71,625 by the time you are 40

$197,943 by the time you are 50

$547,037 by the time you are 60

And $1,114,423 by the time you are 67

is there a function that will give me this answer without using
multiple rows and columns to calculate this.




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


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.


for a particular investment, i have data for my amount invested as well as the corresponding returns.

eg. $1 invested - $2 returns
$3 invested - $5 returns
etc.

I'm trying to regress these data points and obtain an equation for a S curve such that after a certain amount, eg. $10, i'll get the same amount of returns.

however, i cant seem to find a way to achieve this. I've tried using the LINEST function but i still couldnt obtain a curve that i'm looking for.

any help is much appreciated. Thanks!


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


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


This is the first time I have tried conditions, so I apologize in advance if this ia a really simple question.

I am trying to show how many years it will take for a retiree to run out of money.

row 1 is his available money (this in determined with other formulas such as income - expenses ect)

Row 2 is number of years (J2 would be 10 years)

Let's say available money turns negative on the 10th year (tenth column "J")

How can I write a condition statement that will say that says if the amount in row one is positive do nothing, but when it turns negative add row 2 of whatever column it turned negative in?

Example:

A B C D E F G H I J K L
5000 4425 3850 3275 2700 2125 1550 975 400 -175 -750 -1325
1 2 3 4 5 6 7 8 9 10 11 12


You will run out of money in: ? years


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


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?