+ Reply to Thread
Results 1 to 3 of 3

Accrued Interest total with quarterly rate changes

  1. #1
    Registered User
    Join Date
    02-11-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Accrued Interest total with quarterly rate changes

    Summary:
    I have an amount of $170 (D76). The starting date is 7/15/05 (D66). Payment date of interest is 12/15/2012 (D3). Interest rate change on a quarterly basis. These rate amount are located in D32 - D55 and H32-H55. Interest should be compounded daily. I have attached the file for your review.

    Cells K71-K76:
    I need to write a formula that will calculate the related interest on the amounts in D71-D76. Is there a look-up feature that can do this? Or, should I just work out a formula for each quarter period?1291-Working.xlsx

    Thank you for the help.
    Tim

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Accrued Interest total with quarterly rate changes

    the value in D76 is $184.78
    the value in C76 is 170...days
    ??
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-11-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Accrued Interest total with quarterly rate changes

    Stock of a foreign company was purchased on 7/15/05 (D66) and sold on 10/31/11 (E66), with a gain of $2500 (F66). The gain is allocated on a per day basis. The amount of days in the year of sale is 304 (H66) and provides a year of sale gain of $330 (J66). The 304 days in the year of sale is considered non-interest bearing day (no interest is to be added to the amount of this tax). The remaining amount of days of 1,996 are considered interest bearing days - keep reading as it will make since later. These days are counted backwards in time allocated the gain to the appropriate tax year (C71-C76). The gain for each tax year (D71-D76) is taxed on the marginal rate the tax payer had for that year. (This is entered E18-E20 & K18-K20.) The tax for each year is calculated in F71-F76, related foreign taxes are entered in the next cell and subtracted from the tax, provided a net tax increase in H71-H76. Here is were the interest part comes up. Because the tax is from a prior year, interest is to be added to the tax amount. The interest rates and the quarterly period they are for have been entered in C32/D32 - C55/D55 and G32/H55 - G55/H55. I need a formula that will find the interest rates to be used to calculated the interest on each tax year up to the payment dated entered in D3.
    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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