Useful Excel Formulas for Controlling Finance

2024 Author: Malcolm Clapton | [email protected]. Last modified: 2023-12-17 03:44

Excel can do a lot, including efficiently planning finances.

There are hundreds of financial planners online. They are all easy to use but limited in functionality. MS Excel against their background is a real harvester. It has 53 financial formulas for all occasions, and it is useful to know three of them for budget control and planning.

PMT function

One of the most relevant functions with which you can calculate the amount of payment for a loan with annuity payments, that is, when the loan is paid in equal installments. Full description of the function.

PMT (rate; nper; ps; bs; type)

• Bid - the interest rate on the loan.
• Nper - the total number of payments on the loan.
• Ps - the present value, or the total amount that is currently equal to a series of future payments, also called the principal amount.
• Bs - the required value of the future value, or the balance of funds after the last payment. If the argument "fs" is omitted, then it is assumed to be 0 (zero), that is, for a loan, for example, the value "fs" is 0.
• Type (optional) - number 0 (zero) if you need to pay at the end of the period, or 1 if you need to pay at the beginning of the period.

BET function

Calculates the interest rate on a loan or investment based on the future value. Full description of the function.

RATE (nper; plt; ps; bs; type; forecast)

• Nper - the total number of payment periods for the annual payment.
• Plt - payment made in each period; this value cannot change during the entire payment period. Typically, the pt argument consists of a principal payment and an interest payment, but does not include other taxes and fees. If omitted, the ps argument is required.
• Ps - the present (current) value, that is, the total amount that is currently equivalent to a number of future payments.
• Fs (optional) - the value of the future value, that is, the desired balance of funds after the last payment. If fc is omitted, it is assumed to be 0 (for example, the future value for a loan is 0).
• Type (optional) - number 0 (zero) if you need to pay at the end of the period, or 1 if you need to pay at the beginning of the period.
• Forecast (optional) - the estimated value of the rate. If forecast is omitted, it is assumed to be 10%. If the RATE function does not converge, try changing the value of the forecast argument. The RATE function usually converges if the value of this argument is between 0 and 1.

EFFECT function

Returns the effective (actual) annual interest rate when you specify the nominal annual interest rate and the number of compounding periods per year. Full description of the function.

EFFECT (ns; nper)

• NS - nominal interest rate.
• Nper - the number of periods per year for which compound interest is calculated.

There are many ways to make your Excel work easier and faster, and we'd love to expand these lists with your tips.