+ Reply to Thread
Results 1 to 12 of 12

Kinked annual fees in future value of annuity calculations

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Exclamation Kinked annual fees in future value of annuity calculations

    Hi,

    I am struggling with a formula to calculate the future value of an annuity.

    I have an annual contribution (USD 30.000) which I'd like to know the future value of in 40 years. The annual interest rate is 2.5%.

    Each year, a fee (0,2%) must be subtracted from the balance at the end of that year, which is simple enough.

    However, the problem is that the fee is kinked, that is, the fee will be 0,2% of the first USD 500,000 of savings, but only 0,1% of anything above USD 500,000.

    The formula I have been using so far is Formula.png. To account for the fee, I currently subtract the 0,2% annual fee from the 2,5% annual interest rate, but, I do not know how to adjust the formula for the kink.

    I have enclosed a sample workbook. Any help is greatly appreciated.

    Thanks,
    Phill
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Kinked annual fees in future value of annuity calculations

    How much is a business math/algebra problem and how much is an Excel programming program. I am not very knowledgeable about financial math/models, so there could be something about the problem I am overlooking. Looking at it as a math problem, it appears to me at first glance that, once the fv goes above .5E6, then r becomes a function of fv. Excel's standard financial function (FV(), NPV(), XNPV()) assume a constant interest rate, but your effective interest rate will be changing, and I don't see a quick and easy manipulation that converts the variable interest rate to a fixed interest rate. If my quick and incomplete analysis is incorrect and the function reduces to something more easily solved, then let me know (with your superior understanding of financial models, you may have already worked out the math/algebra of the problem).

    After my quick analysis, the easiest programming solution that I see is to build the amortization schedule year by year, rather than try to come up with a single cell formula that can solve the equation. If you arrange it so that each year is in a row, and you pay attention to relative and absolute references, you should only need to program one year's calculation. Then, copy that calculation down 40 rows to get 40 years worth.

    That's what I see. Since I don't know what you know, I have not offered anything specific. Help us understand the problem better and how you want to approach the problem in the spreadsheet. Then let us know what you need help with and we'll help as best we can.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Kinked annual fees in future value of annuity calculations

    First, the "annuity factor" formula in B9 has a typo: it should be =((1+B4-B6)^B3-(1+B5)^B3)/(B4-B6-B5). Note the misplaced right-parenthesis.

    Nevertheless, I'm not sure what your "annuity factor" formula in B9 should apply to. But I believe it is incorrect to use for this problem.

    -----

    I find it is best to start with an amortization schedule. See the "flat fee" table in the "corrected" worksheet in the attached Excel file.

    Without taking the tiered fees into account, the ending balance should be $1,976,938.16, not $1,934,710.49.

    Each year, the ending balance before fees (D15) is the beginning balance plus interest. The ending balance after fees (E15) is D15 less fees based on D15.

    The formulas are:

    C15 (year 1): =$B$2
    D15: =C15*(1+$B$4)
    E15: =D15*(1-$B$6)
    C16 (year n>1): =E15+$B$2

    The correct Excel formula for n years (B3) is: =FV((1+B4)*(1-B6)-1, B3, -B2, 0, 1) .

    The mathematical formula is (pmt>0): pmt * (1+r)*(1-f) * ((1+r)^n * (1-f)^n - 1)/((1+r)*(1-f) - 1)

    The highlighted part is what you call the "annuity factor". (Equivalent mathematical expressions are possible.) Here, "f" is a flat-fee rate. It is not "g" (growth rate). "r" is the interest [sic] rate.

    -----

    However, I do not believe there is a simple formula for the tiered-fee problem.

    See the "tiered fee" table. The only difference is in K15 (corresponding to E15 in the "flat fee" table):

    =J15 - MIN(J15,500000)*$B$6 - MAX(0,J15-500000)*$B$7
    Attached Files Attached Files
    Last edited by joeu2004; 12-12-2019 at 03:09 PM. Reason: critical typo in K15 formula

  4. #4
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Kinked annual fees in future value of annuity calculations

    Thanks for the answers guys, appreciate it.

    A few comments:
    - Unfortunately, setting up the amortization table is not a feasible solution, since the ending balance will be calculated for 1000+ persons at a time in an already large file.
    - Interest and fees are calculated on the ending balance (i.e. there will be no interest in year 1), hence the 1,934,710 ending balance (see picture below).



    I have been researching a solution with tiered / non-constant interest rate for a while now, but still no single-formula result. Any other suggestions?
    Attached Images Attached Images
    Last edited by Phil123456789; 12-17-2019 at 04:05 AM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Kinked annual fees in future value of annuity calculations

    Phil123456789 your attachment is invalid.

    Please upload Excel workbooks directly to the forum using the method stated in the 'gold' banner at the top of the page. Your name is the first word mentioned.

    Thank you.
    Dave

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Kinked annual fees in future value of annuity calculations

    Sorry about the delayed response. I have been struggling with how to present the information below in a manner that would be most helpful to you.

    Quote Originally Posted by Phil123456789 View Post
    see picture below
    I see that you fixed the link. But an image is not as useful as an Excel file, because we must re-enter the data and reverse-engineer any calculation.

    On the other hand, if it is the underlying calculations that you do not understand, that is part of the problem for us to help you with, and an image of an amortization schedule is better than nothing.

    -----
    Quote Originally Posted by Phil123456789 View Post
    setting up the amortization table is not a feasible solution, since the ending balance will be calculated for 1000+ persons
    I understand. I wrote that it is best to start with an amortization schedule, not as a final solution (hopefully), but as a tool for understanding the details of the periodic (annual) calculations.

    After we come to a common understanding of all of your requirements and the calculations, we might be able to provide a solution that does not require amortization schedules.

    -----
    Quote Originally Posted by Phil123456789 View Post
    Interest and fees are calculated on the ending balance (i.e. there will be no interest in year 1), hence the 1,934,710 ending balance
    Of course, interest and fees are calculated on the ending balance. I did that in the amortization schedule that I provided in the attachment to my previous response.

    But the key issues are: (1) whether contributions occur at the beginning or end of the "year" (annual period); and (2) whether fees are based on the ending balance before or after adding interest.

    Refer to the attached Excel file. Each worksheet has an amortization schedule for "flat fee" and "tiered fees".

    ----

    I think the worksheet "contrib at end" reflects the calculations that you have in mind.

    Contributions are at the end of the "year" (annual period); and fees are based on the ending balance before adding interest.

    (For simplicity, "ending balance before adding interest" is the beginning balance, assuming no other contributions and withdrawals during the year.)

    As shown in G53 and column H, the amortization schedule for "flat fees" matches your expectations in B11, based on the amortization factor that you calculate in B9.

    (As I indicated previously, note the important syntax correction to the formula in B9. It is important if "growth rate" in B5 ("g") is non-zero.)

    Column M in the amortization schedule for "tiered fees" shows how to calculate the tiered fees.

    But that model makes little sense to me.

    In order for there to be "no interest in year 1" (and apparently no fees), the contribution would have to be at the "end" of the year 1.

    Bear in mind that "end of the year" is not necessarily a date (e.g. Dec 31). It is the end of an elapsed period of 365 or 366 days.

    When we say that we make an "annual" contribution of $30,000 and we want to know the future value "in 1 year", usually we mean 365 or 366 days later, not on the day of the contribution.

    So, normally, the contribution occurs at the "beginning of the year" (not necessarily Jan 1).

    Thus, the contribution accrues 1 year of interest, and the account is charged 1 year of fees. So, the ending balance should be: $30,000 plus interest minus fees, however they are calculated (see below), not just $30,000.

    Relunctant after-thought.... However, if we are at the beginning of "year 1" (not necessarily Jan 1) and we're talking about contributions that will start at the end of the "year" (not necessarily Dec 31), your calculations are correct.

    -----

    The worksheet "contrib at beg" reflects the calculations for contributions at the beginning of the "year" (annual period).

    Fees are still based on the ending balance before adding interest.

    Again, as shown in G53 and column H, the amortization schedule for "flat fees" matches expectations in B11, based on an amortization factor calculated in B9.

    The amortization factor for contributions at the beginning of the "year" is similar to the amortization factor that you provided, to wit:

    (1+r) * ((1+r)^n - (1+g)^n) / (r - g)

    -----

    The worksheet "fees on int" reflects the calculations for contributions at the beginning of the "year" (annual period).

    But fees are based on the ending balance after adding interest.

    As previously, as shown in G53 and column H, the amortization schedule for "flat fees" matches expectations in B11, based on an amortization factor calculated in B9.

    Again, the amortization factor for contributions at the beginning of the "year" is similar to the amortization factor that you provided, to wit:

    (1+r) * ((1+r)^n - (1+g)^n) / (r - g)

    The key difference is: here, r = (1+B4)*(1-B6)-1 instead of 1+B4-B6, where B4 is the interest rate, and B6 is flat fee rate.

    This is the model that makes the most sense to me, because usually, what you call "annual interest rate" is actually the compounded average growth rate of investments over the course of the year.

    In other words, "interest" (investment returns) accrue throughout the "year" (annual period), and they would be reflected in the ending balance that fees are based on.

    So, it makes sense to me for fees to be based on the ending balance after adding interest to the beginning balance (assuming no mid-year withdrawals).

    But if interest is truly paid annually (e.g. some CDs and bonds; perhaps some employer investment plans), it would make sense for interest and fees to be based on the same beginning balance (again, assuming no mid-year withdrawals), as you intend.

    -----
    Quote Originally Posted by Phil123456789 View Post
    I have been researching a solution with tiered / non-constant interest rate for a while now, but still no single-formula result. Any other suggestions?
    I already explained that there is no single formula that can account for tiered fees based on the annual balances.

    And now you are adding "non-constant" interest rates to the mix?!

    Are there any other changes to the problem statement that you can anticipate?

    The amortization factor formula that you provided allows for increasing contributions by a "growth rate" ("g" in B5).

    Do you intend to add "contributions increasing by growth rate" to the mix?

    How about "non-constant" growth rates?

    And how about "non-constant fee rates"? How about more than two fee tiers?

    -----

    Excel does provide the function FVSCHEDULE to allow for varying periodic interest rates.

    But FVSCHEDULE does not allow for annual contributions, not to mention tiered fees, variable or not.

    -----

    So, after you clarify the details of the calculations, ideally by providing a 40-year amortization schedule for one example (one of mine should be a good start, if not exactly what you want), we can provide a VBA function that effectively calculates the amortization schedule internally and returns the final balance.

    I believe that is the best that you can hope for. Is that what you want?
    Attached Files Attached Files
    Last edited by joeu2004; 12-18-2019 at 11:49 PM. Reason: update attachment

  7. #7
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Kinked annual fees in future value of annuity calculations

    @Phil.... I corrected the calculation of the annuity factor for the "fees on int" worksheet; it now agrees with the amortization schedule. If you downloaded the "annuity tiered fees 2.xlsx" attachment before the timestamp on this posting, download the revised attachment again for posting #6.
    Last edited by joeu2004; 12-18-2019 at 07:07 PM.

  8. #8
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Kinked annual fees in future value of annuity calculations

    @jeou, my apologies for my last response I thought it would be easier with a simpler model to solve the two-tier fee problem, but realize now that your last model definitely makes more sense.

    Technically, contributions occur monthly (end of month), interest is earned daily, and fees are paid monthly (end of month). For simplicity, I left out this part out while finding solution to the tiered fees but in the real model, I will have to add this aspect.

    A few clarifications. There will be:
    - No withdrawals
    - No growth in contributions
    - Constant interest rate (2.5% annually but compounded daily).

    The only non-constant is the annual fee of 0.2% (which is technically subtracted monthly), which decreases to 0.1% once the savings / balance reaches 500.000. Fees will never be more than two-tier.

    If you have the time, I would appreciate the VBA solution. Thanks a lot, let me know if any of the above is unclear.

  9. #9
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Kinked annual fees in future value of annuity calculations

    Quote Originally Posted by Phil123456789 View Post
    If you have the time, I would appreciate the VBA solution.
    My apologies. I intended to do that. But this has been a difficult weekend for me.

    I hope to do that Mon or Tue. If not, perhaps someone else will step in.

    It's a simple function to implement. I provided the details of the calculations in my Excel worksheets.

    Quote Originally Posted by Phil123456789 View Post
    realize now that your last model definitely makes more sense
    Thanks for the clarifications. But it is unclear what you mean by "last model".

    I think you mean the "fees on int" worksheet. Right?

    That seems consistent with the fact that "interest is earned daily, and fees are paid monthly (end of month)".

    And just to be clear about "end of month" v. "beginning of month", do you agree with the following example:

    31 Dec year0: 1st annualized contribution (year1 beginning balance)
    31 Dec year1: valuation of year1 (beginning balance plus interest minus fees; year1 ending balance); 2nd annualized contribution
    ....
    31 Dec year39: valuation of year39; 40th annualized contribution
    31 Dec year40: valuation of year40 ("40 years later"); no contribution

    That is how the "fees on int" worksheet is structured.

    The function I intend to implement will have sufficient flexibility to allow you to choose the timing yourself, like the Excel FV function.

    But your clarification on this point might help someone else implement a simpler function, if I cannot do it soon enough.

    Happy Holidays!

  10. #10
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Kinked annual fees in future value of annuity calculations

    No worries at all - any time within the next week or two would be greatly appreciated.

    Regarding the model, yes, the "fees on int" is the correct one.

    Merry Christmas, and thanks for your time!

  11. #11
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Kinked annual fees in future value of annuity calculations

    Finally got around to implementing the VBA function. See the attachment.

    The VBA function is general enough that you can use it for any of the 3 models, although we seem to agree that "fee on int" with beginning contributions is the correct model for your purposes.

    The VBA function parameters are similar to the Excel FV function, to wit:

    fvTieredFees(rate, nper, pmt, pv, pmtAtBeg, feeTable, feeOnInt)

    "nper" is rounded to an integer internally, unlike Excel FV.

    "pv" must be positive, unlike Excel FV. It defaults to zero.

    You must use "pmt" > 0 for contributions and "pmt" < 0 for withdrawals. Again, that is unlike Excel FV. Also, it is not optional.

    "pmtAtBeg" is like Excel FV "type": 0 or FALSE for payments at the end (after calculating interest and fees); 1 or TRUE for payments at the beginning. It defaults to FALSE, like Excel FV.

    "feeTable" should be a 2-dimensional range or array with 2 columns and 1 or more rows, one for each tier. The first column is the lower limit of each tier. The second column is the corresponding fee rate.

    For a single fee rate, you can pass just the single fee rate as an expression or a single cell reference.

    "feeOnInt": 1 or TRUE if fees are calculated after adding interest to the balance; 0 or FALSE if fees are calculated before adding interest. It defaults to TRUE.

    -----

    Note: The fee is charged at the same frequency as "nper". As you have used it, that is annually.

    You could use "nper" months, adjusting the "rate" and "pmt" accordingly. But then the fee would also be monthly, adjusting the fee table rates accordingly. That is unusual; in my experience, fees are quarterly or less frequent. (I am referring to brokerage fees, not operational fees for each security.)
    Attached Files Attached Files
    Last edited by joeu2004; 01-01-2020 at 09:51 AM.

  12. #12
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Kinked annual fees in future value of annuity calculations

    Excellent solution, thanks a lot man, really appreciate all your help on this one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 23
    Last Post: 11-21-2018, 10:08 AM
  2. Economics help: Kinked demand curve
    By Dyregod in forum Excel General
    Replies: 2
    Last Post: 04-30-2014, 05:15 AM
  3. Present Value of Future Annuity Payments that are Indexed Each Year
    By gordco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-24-2014, 02:31 PM
  4. Replies: 6
    Last Post: 06-08-2012, 11:28 AM
  5. IRR calculations Annual vs Quarterly
    By SimpleSmile in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2008, 08:59 PM
  6. NPV calculations at the end of future years
    By das in forum Excel General
    Replies: 2
    Last Post: 03-19-2006, 08:25 AM
  7. Future value plus annual contributions
    By Ken in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2006, 02:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1