Future Value and accruals

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
alfonsedinkbo
Posts: 4
Joined: Fri Jun 28, 2019 9:41 pm

Future Value and accruals

Post by alfonsedinkbo » Mon Jul 15, 2019 8:47 pm

I'm trying to create a spreadsheet to compare how fees will impact the return on an investment, but I have two confusions that can lead to VERY different results.
Confusion #1: Is interest accrued daily, monthly, or yearly?
Confusion #2: Is the fee accrued daily, monthly, or yearly?

Should I set my equation to accrue interest daily, monthly, or yearly? For example:
Daily accrual = rate/365.25
Monthly accrual = rate/ 12
Yearly accrual = rate

Should I set my equation to split up the fund's fee daily, monthly or yearly? For example:
Daily accrual = fee/365.25
Monthly accrual = fee/ 12
Yearly accrual = fee

I'm using Excel's future value function to do this. Here's a scenario:

I have an initial investment amount of $500. The interest rate is 10% and the fund fee is 0.05% (effective interest rate of 9.95%). I add $500 per month into the fund for the next 30 years. Which, if any of these formulas should I put into Excel?

Accrued Daily:
=FV(0.095/365.25,360,500,500)

Accrued Monthly:
=FV(0.095/12,360,500,500)

Accrued Yearly:
=FV(0.095,360,500,500)

pkcrafter
Posts: 13502
Joined: Sun Mar 04, 2007 12:19 pm
Location: CA
Contact:

Re: Future Value and accruals

Post by pkcrafter » Mon Jul 15, 2019 9:15 pm

I have an initial investment amount of $500. The interest rate is 10% and the fund fee is 0.05% (effective interest rate of 9.95%).
Is this an actual mutual fund or an example? If it's actually a fund, please list the name. Mutual fund fees are taken daily and returns are provided after extracting the expense ratio, so you don't deduct the ER (expense ratio).

Paul
When times are good, investors tend to forget about risk and focus on opportunity. When times are bad, investors tend to forget about opportunity and focus on risk.

User avatar
grabiner
Advisory Board
Posts: 24832
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: Future Value and accruals

Post by grabiner » Mon Jul 15, 2019 9:25 pm

None of these formulas are quite correct, but the monthly accrual formula is closest. Since you are making payments monthly, you need your period to be a month; your "yearly accrual" formula implies one payment per year for 360 years.

The fund reports an annualized return. Therefore, you should use 9.95% return in an annual calculation. For a monthly calculation, you need to take a rate which would return 9.95% when compounded monthly, which is 1.0995^(1/12)-1=.00794; that is close to .0995/12=00829.

Therefore, you would have

=FV(1.0995^(1/12)-1,360,500,500)

Similarly, if you invest $6000 annually, you would use

=FV(.0995,30,500,6000)
Wiki David Grabiner

Topic Author
alfonsedinkbo
Posts: 4
Joined: Fri Jun 28, 2019 9:41 pm

Re: Future Value and accruals

Post by alfonsedinkbo » Mon Jul 15, 2019 10:00 pm

pkcrafter wrote:
Mon Jul 15, 2019 9:15 pm
I have an initial investment amount of $500. The interest rate is 10% and the fund fee is 0.05% (effective interest rate of 9.95%).
Is this an actual mutual fund or an example? If it's actually a fund, please list the name. Mutual fund fees are taken daily and returns are provided after extracting the expense ratio, so you don't deduct the ER (expense ratio).

Paul
This is just an example, for simplicity's sake.

Post Reply