Financial functions in Excel

Financial functions in Microsoft Excel

Excel formula and functions that assist with financial calculations such as calculating debt repayments, consolidating debt and calculating repayments.

Below are some video clips of extremely useful functions built into Microsoft Excel.

Rate video tutorial

Calculate the interest RATE charged on a constant annuity cash flow.

Excel Help File Description: Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Internal Rate of Return/ IRR video tutorial

Calculate the Internal Rate of Return of a series of cash flows

Excel Help File Description: Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

Net Present Value/ NPV video tutorial

Calculate the Net Present Value of a series of cash flows.

Excel Help File Description: Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Calculate the Compound Annual Growth Rate (CAGR) using Excel video tutorial

Use the RATE function to calculate the CAGR

PMT video tutorial

The PMT function calculates the required repayment amount to amortise (pay off) a debt or loan based on the original capital amount, interest charged and term of the loan

Excel Help File Description: Calculates the payment for a loan based on constant payments and a constant interest rate

CUMIPMT & CUMPRINC video tutorial

The CUMIPMT and CUMPRINC functions allow you calculate the total interest or capital (principal) paid between any two periods without having to build an amortization table.

Excel Help File Description: Returns the cumulative interest paid (or principal paid) on a loan between start period and end period. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in (follow this link to learn how).