Financial functions in Microsoft Excel
Excel formula and functions that assist with financial calculations such as calculating debt repayments, consolidating debt and calculating repayments.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Below are some video clips of extremely useful functions built into Microsoft Excel.
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.
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.
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).
Use the RATE function to calculate the CAGR
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
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).