Learning Excel - Compounding
Hello Monkeys,
For a community full of excel experts, I thought this would be the best place to ask my rookie question and have it answered and hopefully, lead me in the right direction and help me learn excel in the process. I am trying something simple. Invest "X" amount in a stock. Take the dividend % at the end of the year and see what I will get by the end of the year. Simple enough. I did that. However, I don't know the formula to do this in excel for 20 years - without having to go in on each sell to calculate the amount by referencing the cells. Paste special didn't help when I pasted the formula. Can someone please assist? I want to take the previous year amount after dividend has been paid, add the Div Yield and do it for 20 years. For simplicity, I kept the Div Yield fixed. I think its the referencing of cell that is messing it up. Any advice would be greatly appreciated. Thanks.
I've attached a rudimentary draft below with the formula to show what I did.
I think the reason this is not working is because you are multiplying the $1000 times (1 + div rate). Therefore, the dividend rate is being calculated as 1.57% of $1,000 when it should be calculated as 1.57% of $190.73, the current stock price.
Yep, I see that now. Made that change, but I am still wondering how I can use excel to find the numbers for 20 years. So making the change to the initial part, I see that div per the 10,000 invested is $157. Which I added back to 10,000. But that is for the first year. How can I use excel to get the second year using the first year's final pay as the reference cell? Do you have a formula for it? For example, if this new figure - $10,157 is in cell A1, how can use this cell to get new figure in cell A2, A3 and so on... which has both the previous interest accrued number and have Div rate added to that going forward for 20 years? I am stumped on that part of this now.
Dude I'm honestly drawing a blank. There seems to be a good resource on investopedia for you to check out, I should know this so well
Deleted
=principal(1+div rate)^(year-1)
yr: 1 2 3 4 5 div rate: 3% princ. 1000
1000*(1+3%)^(1-1)
make sure numbers are hard coded
Deleted this comment
No worries!
Thanks for answering everyone! I am new to this so I am starting with what I thought would get me rolling using some basic math for finance and excel. But I think I played a part in complicating it as well. So, I did it the old-fashioned way to illustrate my point a bit more. If you look at the attached, there is one constant which is the Div Rate. The only thing that changes is the principal at the beginning of each year. I thought there was a formula to keep the constant as it is and take into the new cell the value from previous. I think this is shown by putting the $ symbol in front of cell that I am trying to keep constant, but I most certainly did not input that correctly. If you want to check out the what I mean by what I want to keep constant, maybe the attached link will give you a lightbulb moment that I didn't get.
P.S. This is just some crude start with excel - not meant to be anything remotely presentable.
https://drive.google.com/open?id=1pMl4LeE02nNzVdgadT52qKKoa638MDPq</a">Excel Compound Formula 2
did i not answer your question?
Eum enim sit iusto veniam odio. Beatae in id dolor aut aut sapiente expedita. Qui totam iusto ut aut. Voluptatem nam est ab quod fugiat optio. Beatae rem ex nostrum molestiae omnis qui. Iure assumenda ut officiis magnam accusamus nisi. Quod eius assumenda qui non ut.
Dolor rem sit ducimus ea voluptas. Eius consequatur atque rem quibusdam nulla. Tenetur et sit eum aut eum perferendis vitae velit.
Quod corrupti quasi dolorum architecto est. Assumenda temporibus iusto facere velit harum. Est possimus totam doloremque laborum et quae. Quis dolor ut nemo mollitia voluptatibus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...