Share via

Formula for capitalized interest (interest on interest)

Anonymous
2011-03-24T10:30:54+00:00

I want a formula that returns the acumulated interest for a loan with a fixed interest rate and capitalized interest; i.e. the formula should take into account "interest on interest".The formula should take into account;:

Start date

End date

Interval of capitalization (or similiar)

Interest rate

Amount

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-03-24T10:47:45+00:00

    Hi,

    Maybe something like this, see my data layout below. A1 is investment date. A2 is maturity date. A3 the diference in years(5) worked out with the formula

    =DATEDIF(A1,A2,"y")

    A4 is the annual  interest rate

    A5 is the investment ammount

    And the formula calculates the value at the end of the period

    =A5*(1+A4)^A3

    01/01/2011 £1,402.55
    31/12/2016
    5
    0.07
    £1,000.00

    7 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-03-24T12:41:55+00:00

    Hi,

    The link below is the one I use for function language translations bur DATEDIF isn't there.

    I think but am not sure that DATEDIF will work in the Sweedish version. Have a look at this link

    http://www.excelbanter.com/showthread.php?t=66843

    and this one

    http://www.cpearson.com/excel/datedif.aspx

    http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-24T12:36:53+00:00

    I know that the abbrevations are the formula names, but what would the fulltext name be?

    That would help me to try to find them in swedish.

    0 comments
  2. Anonymous
    2011-03-24T12:31:22+00:00

    I am a little suprised though that Microsoft does not have a premade formula for this.

    Your welcome and thanks for the feedback. Excel does have functions to do this, several of them, specifically:-

    FV

    PV

    NPER

    NPV

    There all well documented in HELP. To see all the financial functions go to

    Formulas tab|Insert function and select 'Financial' from the dropdown.

    You may also like to mark my response as answer.

    EDIT.. You won't find DATEDIF documented in Excel, it's a bit of a wild child and can misbehave producing odd results if used in some ways.

    0 comments
  3. Anonymous
    2011-03-24T12:15:13+00:00

    Thanks Mike!

    Since i work in a swedish version, I did not find the "DATEDIF"-formula.

    Instead i put A3=(A2-A1)/365 which works fine. Otherwise I worked with your formula.

    I am a little suprised though that Microsoft does not have a premade formula for this.

    Thanks again Mike!

    0 comments