+ Reply to Thread
Results 1 to 9 of 9

Compound Interest Calculations in Excel

  1. #1
    Registered User
    Join Date
    04-05-2019
    Location
    Cambridge
    MS-Off Ver
    10
    Posts
    5

    Compound Interest Calculations in Excel

    Hi Guys,

    What I want to be able to do is to calculate a compounded value from a set of variables in order to calculate a lifetime contract value. The variables are:-

    Contract Value - CV
    Number of Years Remaining on Contract - YR
    Annual Interest Uplift% - AU

    To give an example, a customer's contracted value is £10,000 (CV) the Annual Uplift is 6%, this is simple to calculate for year one, the answer is £10,600. The second year is £10,600 + 6% = £11,236 and the third year would be £11,236 + 6% = £11,910.16 - so the LCV (assuming three year contract) is £10,600+£11,236+£11,910.16 = £33,746.16

    I have scoured the net and there are lots of solutions that will add compounded interest to the initial amount over a number of periods, but I have not found where I can set a number of years, give a contract value and an annual uplift and get the LCV - Help Please!

    Regards,

    Tim

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

    Re: Compound Interest Calculations in Excel

    For your example:

    =FV(6%, 3, -10000, 0, 1)

    The FV function relies on signed cash flows: plus or minus for inflows; the opposite sign for outflows. I chose minus for inflows (10,000) so that the FV function (outflow) returns a positive value.

    Caveat: The FV function will not round to 2 decimal places each year. So you might encounter some differences over longer terms. For example, for 6, years, the FV function returns $73,938.38, whereas a manual calculation with rounding each year results in $73,938.39.

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Compound Interest Calculations in Excel

    another alternative:

    =SUMPRODUCT(B1*((1+B2)^ROW(A$1:INDEX(A:A,$B$3))))

    where B1 holds CV, B2 holds AU and B3 holds YR

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Compound Interest Calculations in Excel

    dont know a function for that but you can always build your own function (UDF)with VBA
    Please Login or Register  to view this content.

    see attachment for testing
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-05-2019
    Location
    Cambridge
    MS-Off Ver
    10
    Posts
    5

    Re: Compound Interest Calculations in Excel

    Perfect - thanks. How about in reverse now - Say I have an end figure of £33,746.16 and know the term is three years - How could I reverse the process so I can plug in those two variables and get back to £10,000 - my starting point?

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

    Re: Compound Interest Calculations in Excel

    Joeu2004 really seems to know his way around these financial functions. If he says that the FV() function is appropriate for this, then I assume that the rest of the PV() family of functions can be used to compute any of the variables in this kind of problem. If you need the present value for a given future value, you can use the PV() function. If you need the interest rate, use the RATE() function, and so on. I might suggest that you start with the PV() help file https://support.office.com/en-us/art...1-da16e8168cbd
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    04-05-2019
    Location
    Cambridge
    MS-Off Ver
    10
    Posts
    5

    Re: Compound Interest Calculations in Excel

    Many thanks, just tried the help topic but cannot fathom how to do this - the formula requires PMT but there are no payments made. I want to be able to work out from the future value - what the present value is based on only a term (years), interest rate (%) and future value (£) - any ideas what the formula would look like please?

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Compound Interest Calculations in Excel

    See if the attached is of any use.
    Used for considerations of interest only mortgages and equity release.
    torachan.
    Attached Files Attached Files

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

    Re: Compound Interest Calculations in Excel

    Quote Originally Posted by Tim4971 View Post
    How about in reverse now - Say I have an end figure of 33,746.16 and know the term is three years - How could I reverse the process so I can plug in those two variables and get back to 10,000 - my starting point?
    Notice that in my FV formula, 10000 is the "pmt" parameter, and I use type=1 (payment in the beginning).

    So we might guess that the reverse uses the PMT function, to wit:

    =PMT(6%, 3, 0, -33746.16, 1)

    (Caveat: Normally, I would use FV(6%,3,-10000,0,1) instead of 33746.16. Over a longer period of time, the FV value might be more precise than the result rounded to 2 decimal places. Using the latter might not "reverse" the calculation precisely.)

    Again, Excel expects signed cash flows for these functions: plus or minus for inflows; the opposite for outflows. I choose minus for the "fv" parameter so that PMT returns a positive value. (Personal preference.)

    Don't get hung up on the terms "inflows" and "outflows". They depend on your point of: borrower v. lender; investor v. broker or bank; annuitant v. annuity. We can adopt either point of view, whichever is convenient for the calculation.

    Also, don't get hung up on the terms "fv" and "pmt". I am simply using the underlying mathematics of these functions to calculate the arithmetic that you describe.

    I confess: I don't know what you mean by "lifetime contract value" and "uplift". (Well, the meaning of "uplift" became obvious from your description.) I presume that we use different terminology in the States. But a google search turned up nothing relevant.

    You described the sum of 3 cash flows, each of which is 6% greater than the previous, and the first of which is 6% greater than 10000. Mathematically, we can write:

    10000*(1+6%) + 10000*(1+6%)^2 + 10000^(1+6%)^3

    I recognize that as the "future value" of an investment over 3 periods with a "fixed contribution" of 10000 at the beginning of each period. (Technically, the terms of the summation series are reversed. But the associative law of arithmetic tells us that does not matter.)

    So, the FV function with type=1 gives us that sum.

    -----

    However, if I knew what a "lifetime contract" (value) is in USA terminology, I might recognize a mistake, if any, in your description or assumptions.

    For example, perhaps the LCV should be the "present value" that is required to fund an annuity (series of payments) that is 10600 (or 10000?) for the first period, and increasing by 6% each period thereafter.

    You might incorrectly believe that is 33,746.16, the sum of the annuity series. Or you might incorrectly believe that is 10,000. It is not. (But in another response, you say that you want to calculate ``what the present value is``.)

    Alternatively, of course, you might know what you are talking about, and everything is fine.
    Last edited by joeu2004; 04-05-2019 at 05:15 PM.

+ 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: 5
    Last Post: 06-29-2017, 11:18 PM
  2. [SOLVED] Compound interest
    By kamaflage in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2016, 11:50 AM
  3. Compound Interest
    By Helencool in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-22-2014, 04:50 PM
  4. Need help with complex Excel problem. Compound Interest.
    By MikeSeattle in forum Excel General
    Replies: 6
    Last Post: 09-19-2012, 12:21 PM
  5. Replies: 3
    Last Post: 02-14-2008, 11:27 AM
  6. [SOLVED] Mtge calculation (Dly compound interest and multiple interest rate
    By Spudson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2005, 03:55 PM
  7. Mtge calculation (Dly compound interest and multiple interest rate
    By Spudson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

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