+ Reply to Thread
Results 1 to 2 of 2

FV where interest rate is calculated daily but compounds monthly, and r can change daily

  1. #1
    Registered User
    Join Date
    07-08-2018
    Location
    South Africa
    MS-Off Ver
    365
    Posts
    1

    FV where interest rate is calculated daily but compounds monthly, and r can change daily

    Hi all

    I'm struggling to find a quick way to calculate the FV of a deposit where interest is calculated daily but compounded monthly, with the complication that the interest rate can change at any arbitrary day, and the deposit is not necessarily made on the first day of the month. I currently make use of amortization tables but need to streamline the process due to the volumes. I am attaching a simplified example of my problem. Is there a way to do this with formalae in Excel?

    - hoping someone can point me in the right direction!

    Thanks
    Attached Files Attached Files

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

    Re: FV where interest rate is calculated daily but compounds monthly, and r can change dai

    Short but useless answer -- yes, there is a way to do this with formulae in Excel.

    Obviously, the built in FV() and similar functions are built to assume evenly spaced payment periods and a constant interest rate. Something like the XNPV() function does not need evenly spaced periods, but it does assume a fixed interest rate.

    I firmly believe that I must first understand the math/logic behind a problem before I can program the solution into Excel. So I put something like "future value with variable interest rate" into my favorite search engine and got nothing that helped me (a non-financial person) understand how one should calculate future value when the interest rate varies. I'm afraid I cannot help you at this point.

    In order to help you help us get you a solution, I recommend a couple of things. One, make it clear whether this is specifically an Excel question (I know how to calculate this future value outside of Excel, but am having trouble moving the calculation into Excel) or a broader business math question (I don't know how anyone using any programming language or even paper, pencil, and hand calculator performs this calculation). If it is the latter, then you will need to wait until someone comes along who really knows business math who can explain this calculation to you. If it is the former, then help us non-business-math people understand the sequence of calculations that goes into this, and we'll help you put it into Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Calculate the daily interest rate on an overdue invoice
    By Ralph Imbrogno in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2014, 04:25 PM
  2. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  3. Replies: 0
    Last Post: 07-25-2012, 07:08 AM
  4. Interest calculated daily and charged quarterly
    By beccyc in forum Excel General
    Replies: 1
    Last Post: 10-18-2011, 10:53 PM
  5. daily rate monthly sums
    By oregonlahar in forum Excel General
    Replies: 1
    Last Post: 04-09-2010, 04:06 PM
  6. Calculate daily interest rate
    By Beechams in forum Excel General
    Replies: 2
    Last Post: 11-09-2008, 06:58 AM
  7. How Do You Calculate On-Going Daily/Monthly Interest?
    By StevenLAX in forum Excel General
    Replies: 1
    Last Post: 03-01-2005, 09:23 PM

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