Excel Financial Formulas

# Excel Financial Formulas

July 31, 2017

Here's a brief rundown of some financial formulas available in Microsoft Excel.

Â

In the screen grab below column E shows the text of the formulas listed in column C.

Â

Â

Â

Â

The CUMIPMT formula calculates the total interest paid on a loan between two periods:

=CUMIPMT(Interest Rate/12,Length of Loans in Years * 12,Loan Amount,First Period,Last Period,[0 if the payment is made at the end of the period, 1 if the payment is made at the beginning]).Â

=CUMIPMT(.03/12,30*12,500000,1,360,0)

Â

Â

Â

The CUMPRINC formula calculates the amount of a principal paid between two different periods.Â

=CUMPRINC(Interest Rate/12,Years of Loan*12,Loan Amount,First Period, Last Period,[0 if the payment is made at the end of the period, 1 if the payment is made at the beginning]).Â

=CUMPRINC(B2/12,B3*12,B4,1,360,0)

Â

Â

Â

The DOLLARDE formula converts a fraction of a dollar into a decimal amount. Â  For example:

=DOLLARDE(1.1,2)Â

. . . will find the the decimal amount for 1 over 2, or .5.

=DOLLARDE(1.04,16)Â

. . . will find the decimal amount for 4 over 16, or .25.Â

Â

Â

The EFFECT formula shows the effective annual interest rate based on the number of times interest is compounded. Â The compounding period is the number of times that unpaid mortgage interest is added to the principal amount of the loan.

=EFFECT(Interest Rate,Compounding Periods)

=EFFECT(0.03,4)

Â

Â

Â

TheÂ FV formula calculates the total value of an annuity. Â  Â

=FV(Interest Rate per Period,Number of Payments,Payment in each Period,Present Value,[0 if the payment is made at the end of the period, 1 if the payment is made at the beginning]).Â

Â

Â

Â

Â

Â

Â