How to get stock market return value?

[firelight]

I loaned a large amount of money to a friend in early 2010 and continued giving money till now. We agreed that he will return it at stock market return rate. How do I calculate the stock market return for various dates? Since he will be returning it over a period of time, we would prefer if the stock market return reflects what's happening in the market every day or week.

For example, say I gave him $1000 on Jan 1 2010. Investing in stock market (vtsax) would've given me around $1800   as of now. If he returns it today, he'll give $1800. Say he returns it in a month by which stock market gives $2000. We are looking for a way to automatically update the spreadsheet we have for tracking without having to manually enter it every time. Simple reason, the money I gave him varies in amount and duration and he can't return it all at the same time. So return would be over a period of time as well.

Does anyone know about any tool/spreadsheet that can help us with it? Any other ideas welcome too.

[GizmoTX]

Suggest you settle on the rate(s) in place when you made the loans & concentrate on getting your money back. The rate should not be dependent upon future unknowns.

[ken]

It seems very random and foolish to tie the payback to the stock market. Focusing on monthly fluctuations is like market timing, a gamble. As the other poster said, focus on getting your money back with a reasonable/fair interest rate. Your friend should put his man-hour toward earning money to pay you back, not managing a spreadsheet.

[Terrestrial]

if you are really set on doing this then the easiest way is to decide what index you will base a 'stock market return' on...say the S&P 500, and then download an .xls or .csv file that has the values on a monthly basis.  These are easy to find, for example from this website:

http://datahub.io/dataset/standard-and-poors-500-shiller

From there it is relatively easy to construct a column that shows what the % return was from some past date to the present and should allow you to enter the various amounts that you gave him and at which dates, and have it calculate the present value of that money based on the return from those dates.  You could create a spreadsheet like this after downloading the seed data in maybe half an hour if you wanted to.  To keep it up all you have to do is update the spreadsheet monthly with a new value for the S&P and it will automatically update.

However, I would echo the others that this isn't probably the best way to handle it.  For one you started loaning him money at a time when hard interest rates were almost zero and the market has about doubled since 2010 so even if you do get him to pay you double, he probably wont be a friend for long even if he agreed to it.

In this case your arrangement will work out to your huge advantage but just as easily could have been reversed.  For that matter if he waits long enough with his repayment and you did not agree to set terms on repayment time, if i was him i would just wait until there is a market correction and then pay you back while the markets are down.  Turnabout is fair play.

The easiest and perhaps most fair way to handle this is to just go by the 'historic market return' and have him pay you back at a 10-12% rate and agree on a repayment schedule.  This is advantageous for both of you, this is a far better rate than you would likely have gotten by lending to anybody that wasn't a moron about the terms (with all due respect to your friend).  He would have taken a cash advance on a low interest CC for the money and been better off than the terms you offered (in hindsight, as i mentioned it could have worked out great for him if the market had tanked).

[S D]

lol maybe your friend should wait to pay you back until the market tanks

i attached an excel file that should do what you want (but you should double check the calcs). just put in loan amounts as a positive value and the payments as a negative value in the amount column. there is also a button that will download the most recent data from yahoo finance and you can choose what ticker symbol and price to use (the adjusted close makes the most sense, otherwise it doesn't account for dividends and splits).

[thedayisbrave]

This doesn't really make sense to me.  Why not just agree on an interest rate that is fair to both parties and be done with it?

[firelight]

I wanted to do a rate of 7% but he doesn't want to... Feels he cheated me out of stock market return due to the huge growth period we just had. Will try talking to him again.

Thanks a lot for all ideas!

[GizmoTX]

Then have him pay you whatever interest he thinks is fair, as long as the interest is at least 7%.
You chose to lend to him rather than invest that money in the market.
The important thing now is the payback amount & number of payments. "Try talking" doesn't sound promising.

[JGB]

What happens under your agreement if the stock market loses value? That would definitely be a factor is you are looking at the rate from one week to the next.

To answer your original question, I think you could track the changes in the Dow as a reasonable method for tracking the return of the market. But I agree that this is a silly way to handle the rate for a loan, especially if it is examined on a short term basis.

[firelight]

We finally came to an agreement where he pays me a rate of 7% + whatever he thinks is fair. I told him using the stock market growth is silly and has too much fluctuation and though he still feels bad about it, this was the best I could convince him of.

[firelight]

I've given him some time to come up with one rate so we can stick to it but he is very resistant to the push....

[mozar]

Sound like he's stalling. Can you get your original amount back then discuss the rest?

[GizmoTX]

I agree that it sounds like he's stalling.
Did you agree to any terms when you made the loan?

[Holyoak]

This dude who NEEDS $$$ from you (lender), is pissy/calling the shots/revising/stalling on the terms...  Tell em to go to a bank (bank won't lend to him?) and try this BS.  Something about this feels like a train wreck just up the tracks, and I hope you get back your dough.

[retired?]

It's a whacky idea and I agree with the comments that the friend is stalling. 

But, I'll toss out an alternative.  Reset the principal to be the amount you'd have currently if the stream borrowed had been put into that stock index.  Easy to do if you have the historical prices on the dates money was lent.  Then choose an interest rate for the payments going forward.  Fixed or floating.  You get the benefit of having tracked the market so far, but avoid the hassle of (possibly) subjective calculations.

I have done something similar with a relative.  I lent money with payments starting in 5 years, but if they cannot afford the original terms, we'll reset based on the environment at that time......i.e. if rates had gone up, the reset rate would be higher.  Flexibility, but still market based.