Microsoft Excel has 15 built-in financial functions. The Insert/Function... dialog box lists each function together with a short description. This concept will examine the most useful financial functions. The others are similar and should be easy to learn once you understand the concepts presented here.
=PMT(rate, nper, pv)
The PMT function is a built-in function that computes the periodic payment given the rate, number of periods (term or number of payments), and present value (loan amount).
For example, if you borrow $12,000 at 8.5% for three years, your monthly payment would be:
=PMT(.085/12,36,12000) = $-378.81
The subtle point to notice about the way this function is used is that the rate is the interest rate for a single compounding period and term is the number of compounding periods. Also, note that Excel displays the result as a negative number because it represents an outflow of money. Not all spreadsheets follow this convention.
Microsoft Excel doesn't have a function for directly computing the return on an investment. Since the cost to a borrower is the return to an investor, the PMT function can be used to calculate the return on an investment.
For example, if you invest $14,000 at 5% for three years, at the end of 3 years you would have:
=PMT(.05/12,36,14000) * 36 = $-15,105.33
For more information about how this function is used, see the related concept on amortization tables.
=FV(rate,term,payment)
The FV function computes the future value of a regular investment earning a fixed rate of interest over a given term.
For example, if you invest $500 a month for 4 years at 4.5%, at the end of 4 years you would have saved:
=FV(.045/12,48,500) = $-26,241.92
=PV(rate,term,payment)
The PV function computes the present value of a regular investment earning a fixed rate of interest over a given term.
For example, your grandparents want to lease a new Ford LTD for three years. The lease price is $400 a month and the current interest rate is 5%. They want to know how much they need to have set aside today to cover the total cost of the lease. The present value of $400 invested monthly for three years is:
=PV(.05/12,36,400) = $-13,346.28