Results 1 to 10 of 10

calculate interest per day

This is a discussion on calculate interest per day within the Excel Questions forums, part of the Question Forums category; Hello all, Total loan amount is 200,000.00 Interest rate is 6.95% Interest for 15 days is 38.6111 per day My ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327

    Default calculate interest per day

    Hello all,

    Total loan amount is 200,000.00
    Interest rate is 6.95%
    Interest for 15 days is 38.6111 per day

    My question is what would the formula be to arrive at 38.6111 for 15 days?

    Thanks for any help
    James

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,058

    Default Re: calculate interest per day

    Quote Originally Posted by James
    Hello all,

    Total loan amount is 200,000.00
    Interest rate is 6.95%
    Interest for 15 days is 38.6111 per day

    My question is what would the formula be to arrive at 38.6111 for 15 days?

    Thanks for any help
    James
    Maybe one of theses from the Help files,

    ACCRINT Returns the accrued interest for a security that pays periodic interest

    ACCRINTM Returns the accrued interest for a security that pays interest at maturity

    AMORDEGRC Returns the depreciation for each accounting period by using a depreciation coefficient

    AMORLINC Returns the depreciation for each accounting period

    COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date

    COUPDAYS Returns the number of days in the coupon period that contains the settlement date

    COUPDAYSNC Returns the number of days from the settlement date to the next coupon date

    COUPNCD Returns the next coupon date after the settlement date

    COUPNUM Returns the number of coupons payable between the settlement date and maturity date

    COUPPCD Returns the previous coupon date before the settlement date

    CUMIPMT Returns the cumulative interest paid between two periods

    CUMPRINC Returns the cumulative principal paid on a loan between two periods

    DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method

    DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify

    DISC Returns the discount rate for a security

    DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number

    DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction

    DURATION Returns the annual duration of a security with periodic interest payments

    EFFECT Returns the effective annual interest rate

    FV Returns the future value of an investment

    FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates

    INTRATE Returns the interest rate for a fully invested security

    IPMT Returns the interest payment for an investment for a given period

    IRR Returns the internal rate of return for a series of cash flows

    ISPMT Calculates the interest paid during a specific period of an investment

    MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100

    MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates

    NOMINAL Returns the annual nominal interest rate

    NPER Returns the number of periods for an investment

    NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate

    ODDFPRICE Returns the price per $100 face value of a security with an odd first period

    ODDFYIELD Returns the yield of a security with an odd first period

    ODDLPRICE Returns the price per $100 face value of a security with an odd last period

    ODDLYIELD Returns the yield of a security with an odd last period

    PMT Returns the periodic payment for an annuity

    PPMT Returns the payment on the principal for an investment for a given period

    PRICE Returns the price per $100 face value of a security that pays periodic interest

    PRICEDISC Returns the price per $100 face value of a discounted security

    PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity

    PV Returns the present value of an investment

    RATE Returns the interest rate per period of an annuity

    RECEIVED Returns the amount received at maturity for a fully invested security

    SLN Returns the straight-line depreciation of an asset for one period

    SYD Returns the sum-of-years' digits depreciation of an asset for a specified period

    TBILLEQ Returns the bond-equivalent yield for a Treasury bill

    TBILLPRICE Returns the price per $100 face value for a Treasury bill

    TBILLYIELD Returns the yield for a Treasury bill

    VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method

    XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

    XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic

    YIELD Returns the yield on a security that pays periodic interest

    YIELDDISC Returns the annual yield for a discounted security; for example, a Treasury bill

    YIELDMAT Returns the annual yield of a security that pays interest at maturity

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Location
    Hawaii
    Posts
    284

    Default Re: calculate interest per day

    or one of these from MSKb:

    ===============
    http://support.microsoft.com/search/...false&numDays=

    XL: Function to Compute Interest and Growth Rate on a Single Payment
    (214113) - In Microsoft Excel, the RATE function assumes a stream of payments. If you want to compute the interest rate for a single payment (present value) over a given period, use the following formula = ((FV/PV)^(1/N))-1 where: PV equals the value today...

    XL: How to Compute the Periodic Annual Interest Rate in Microsoft Excel
    (110854) - The RATE() function in Microsoft Excel returns the periodic interest rate necessary for an investment to grow to a specific value over a specified number of compounding periods. However, the RATE() function assumes a known periodic payment amount. If...

    XL: Examples of Interest Calculations Outside the United States
    (294396) - Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article provides examples that demonstrate how to compensate for these differences...

    XL: Finding the Balance of a Loan for a Given Period
    (214091) - To calculate the balance of a loan for any period during the life of the loan, use the IPMT function to calculate the interest payment for the next period and divide it by the interest rate of the loan.

    XL: How to Compute the Average Growth Rate of an Investment
    (123198) - The "average growth rate" is a calculation used by financial investors to determine the best investment over time given present value, future value, and number of periods per year of an investment. This calculation can also be referred to as an...

    ===============

    What is it you're working on? Your question doesn't exactly make sense.

    Tana-Lee

  4. #4
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: calculate interest per day

    Actually, I get $38.0822 as the interest per day. Here's how:

    200,000.00 in A2 6.95% in B2 =A2*B2/365 in B3, I get the above amount in cell B3.

    Does this answer your question?

  5. #5
    Board Regular
    Join Date
    Jul 2003
    Location
    Hawaii
    Posts
    284

    Default Re: calculate interest per day

    Aloha, Ralph...

    I'm guessing your post might not answer the question. Note:

    Interest for 15 days is 38.6111 per day

    ...What would the formula be to arrive at 38.6111 for 15 days?

    ???????????

    Tana-Lee

  6. #6
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: calculate interest per day

    Now, Tana Lee, look here:
    Hello all,

    Total loan amount is 200,000.00
    Interest rate is 6.95%
    Interest for 15 days is 38.6111 per day

    My question is what would the formula be to arrive at 38.6111 for 15 days?

    Thanks for any help
    James
    James's third line is "Interes for 15 days is 38.6111 per day".
    James' fourth line is "...to arrive at 38.6111 for 15 days?"

    I do think James is a little mixed up, but, "Interest for 20 years is STILL 38.6111 PER DAY", as long as the amount and % interest rate don't change.

    if James means, "interes for 15 days", he should stop there, and the correct anser would be, 38.08219178*15 = $571.2329 (he indicates four decimals, so, that's how I show it, even though only two decimals are ever used as a final figure).

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327

    Default

    Tana Lee / RalphA thanks for the help
    It turns out Ralph clocked it.
    200,000 x 6.95% = 13,900.00 / 360 = 38.61

    Thanks again
    James

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: calculate interest per day

    James,

    See this thread regarding how financial institutions may define “daily interest”.

    http://216.92.17.166/board2/viewtopic.php?t=51066

    I’m pleased that you received an answer for your particular problem. However, applying that formula in another circumstance may not produce the expected result. In that situation, the financial institution may have adopted a particular definition of “daily interest”.

    A 360 day year is referred to as "Bankers Rule" (12 x 30-day months). It is much easier for financial institutions to work interest calculations based on an even 30 day month, then to use actual days. The main point to note is that for any given nominal rate of interest, the effective rate of interest will be greater when a 360 day year is used in the interest calculation than when a 365 day year is used.

    Regards,

    Mike

  9. #9
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: calculate interest per day

    I might add that in the past two years, I have not found one bank (there may be, however) that doesn't use ACTUAL days, even 366 for a leap year, in doing their calculations.

    So, yes, ALWAYS ask the particular banking institution to explain exactly how they calculate interest for your particular case.

  10. #10
    Board Regular
    Join Date
    Nov 2003
    Posts
    379

    Default

    Hi,
    You can try this by naming the cells,
    Total loan amount is 200,000.00 = p
    Interest rate is 6.95% =R
    Interest for 15 days =T

    =p*r*t/365 your formula becomes dynamic

    Try this and confirm
    Prakash

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com