Create an Investment Fund Calculator with Microsoft Excel

[@nancisailormoy9135]

This is just what I was looking for to calculate my investments for retirement. Do you have this spreadsheet saved somewhere?

[@daniahmadriyadi]

Hatur nuhun kang...

[@ngee4925]

Pretty cool Ralph. Is there a way to compare 2 funds based on different expense ratios and different annualized returns?

[@gautamvv]

Great video, How can I get nasdaq and s&p averages for past 20 years, where was your source of information ?

[@K4lr0b]

Awesome, I will use this to calculate my future gains. Thank you!

[@francinelong7806]

🙂Thank you for this. I will be using this in my math class.

[@86MS86]

Now add the fact that you invest in an ETF with an APR of 0,3% and that you have to pay tax of 17% at the end of each year. Could you add this to the calculation?

[@SixMinutesSmarter]

it would be an interesting formula addition. separate the gains from the basis and remove 17% every 12 months. I did this one at 500/month or 600/year as it coincides with roth ira limits. No taxes on the gains-especially since these are long term investments.

[@joshuaklein4513]

Hi Ralph, is there a way to change it from monthly to annually?

[@SixMinutesSmarter]

Sure is, and it’s easy. Of course your dates should be set to display year by year. The formulas are pretty much the same, but instead of dividing the annual rate by 12 (to get a monthly rate), you can leave the rate in annual terms.

[@peterpepo9232]

The average is calculated wrong, isn't it? Let's assume: 2016: -50%, 2017: +50%. Average is 0%. This obviously isn't right. That is the way, that excel calculates average!

[@SixMinutesSmarter]

working with those percentages is weird, right? -50% and 50% won’t make 0. If i have $100 and it goes down 50% (1/2 of 100) then I’ll have $50. If that $50 goes up 50% (1/2 of 50) I’ll have $75. So, over the two years my $100 has dropped to $75 which is a -25% return. over 2 years that can be thought of as an average of -12.5%. Even the -12.5% is an approximation because the average annual return should be weighted. -12.5 is close, though.

[@peterpepo9232]

@@SixMinutesSmarter - I believe, the 'correct' way to to calculate "average" is to to use Annualized Total Return (www.investopedia.com/terms/a/annualized-total-return.asp). In our example - this results in average loss of 13,3974596%. My $100 goes to -> 86,602 after 1 year -> 75,000 after two years. Same mistake for the variable calculation. Let's count for 1 year only. If you divide 50/12 = 4,16667%. For sake of simplicity let's invest $100 in january and none in remaining months. Using your method you end up with $163,20 {[100*1,04]*1,04}*1,04 and so on. This isn't right. If you use Annualized Total Return instead, you get "average" of 3,4366083%. Applying this continuously 12 times in row on your $100 you have: 100 -> 103,436608313192 -> 106,991319393367 -> 110,668191970033 -> 114,471424255335 -> 118,405358737523 -> 122,474487139161 -> 126,683455545725 -> 131,037069710448 -> 135,540300541481 -> 140,198289777615 -> 145,016355859065 -> 150,000000000006 I like the video, in fact this inspired me to calculate my own expected returns prediction. The figures just didn't seem right to me. If you like and find my comment useful, feel free to pin in order to help other people :)

[@sehermehta2376]

god bless you, this is a life saver

[@pranjivans]

Is the average returns on the S&P 500 the 1 year return for a specified year. ?

[@shripalmehta9611]

Dear Sir can I get this excel file to download ?

[@majiddehbi9186]

hi ralph it's my first visit very exciting

[@uschan5227]

but you did not consider expenses. expenses play major role in buying monthly

[@SixMinutesSmarter]

A major role in buying monthly? For long term investing, mutual funds with low expense rates, no transaction costs, and roth ira tax protection is reasonable.

[@uschan5227]

@@SixMinutesSmarter in EU i know that cost about 5 euro for evey buy or sell. so 5/500 is about 1 percent monthly. in addition we have also pay the taxes

[@SixMinutesSmarter]

Interesting. So our typical brokerage buying stocks or ETFs also charge a flat fee for transactions ($6 per trade), but that’s avoided going through mutual fund company and sticking with their family of funds. I see your point about paying that fee for a small monthly. I would wait several months and make a larger transaction. Does EU have a tax free label like the US roth IRA? We can invest up to $6000 per year into a Roth and all growth from that is tax free as long as we withdraw after 59.5 years old.

[@TheSamooot]

Thanks for the video. Could you please tell me where did you get the s&p 500 historical data from ?

[@SixMinutesSmarter]

I don’t recall the specific site, but www.macrotrends.net/2526/sp-500-historical-annual-returns has the data in a nice table format that can easily paste to Excel. You can also use randbetween to creat random returns going forward.

[@TheSamooot]

Thanks for the info. Highly appreciate it 😊

[@peristiloperis7789]

Hi, Ralph! Man, it's been a long time! It's nice to see new videos from you.

[@SixMinutesSmarter]

Peristilo peris Thanks. Been doing a lot of unlisted vids for classes, but time to make more public ones.

[@rahulrsaha]

is the calculation of the investment on compound interest Ralph??

[@spooler24]

After watching the vid, i would assume yes

[@BenjaminABurgessCathey]

Thanks Ralph. This is very helpful. To calculate the same on a yearly basis, should we just not divide by 12? I did so, but the end result doesn’t add up to the same as in your video when re-multiplied by12 to derive an annual estimate. Also, are there calculations to address interest compounded daily, quarterly, yearly?

[@gkollias14]

great video, simple yet elegant

[@SixMinutesSmarter]

Thank you! Cheers!