Before we dive into Excel formulas, let’s do a short summary of financial math concepts. This article will gradually serve as a map of articles – I will add links to articles dedicated to the respective topics.
Financial Mathematics – Basic Concepts
- Debtor – an entity that borrows money
- Creditor – an entity that lends money to the debtor
- Principal (Nominal loan amount) – the borrowed amount, which increases with interest
- Loan Maturity – the date when the entire loan will be repaid, assuming it is repaid as expected
- Installment – usually represents a regular payment for a certain period (typically 1 month). It consists of 2 components – interest and principal repayment
- Principal Repayment – repayment of the principal amount (borrowed sum)
- Interest – the reward to the creditor for lending money to the debtor in a currency (e.g., USD). It is an increase in the borrowed amount.
- Interest Rate (Cost of the loan) – expressed in percentage (%) and represents the increase of the borrowed amount as a reward to the creditor for lending money.
- Nominal Interest Rate – the cost of money not considering inflation
- Real Interest Rate – the cost of money including the inflation component (higher than nominal rate)
- APR (Annual Percentage Rate) – represents the interest rate including additional loan costs such as fees not part of the nominal interest. Therefore, APR is a more important indicator of the loan cost than the nominal interest rate.
- Types of Interest Calculation – there are several ways to calculate interest. Basic types include:
- Simple Interest – in simple terms: calculated interest is not added to the principal, so there are no “interest on interest” effects in the next period. Interest is calculated only on the original principal.
- Compound Interest – here, calculated interest is added to the loan over time and then earns interest itself. “Interest on interest” accrues.
- Anticipatory Interest (Advance) – interest is paid at the beginning of the interest period
- Deferred Interest (Arrear) – interest is paid at the end of the interest period
- Interest Payment Frequency – defines how often interest is paid, typically monthly, but can also be quarterly, semi-annually, or annually. For corporate loans, interest can sometimes be paid only at the end of the term. Interest and principal repayment frequencies can differ!
- Principal Repayment Frequency – defines how often principal repayments occur, frequencies can be the same as for interest (see above). Interest and principal repayment frequencies can differ!
Financial Mathematics – Interest Calculations
| Calculation / Concept | Description | Article Link |
| Future Value (FV) | Shows the future value of a single lump sum deposited today for a specified period at a given interest rate. | Future Value and Compound Interest |
| Present Value (PV) | Calculates the present value – the amount that must be deposited today at a given interest rate to reach a specific future account balance. | Present Value – Opposite of Future Value in Compound Interest |
| Future Value of an Annuity (FVA) | If equal amounts are deposited regularly over a period, this calculates the final account balance (Future Value of an Annuity) at the end of the period. | Future Value of an Annuity – Formulas with Example |
| Annuity Payment to Target FV (Sinking Fund) | Calculates the required regular deposit (annuity payment) needed to accumulate a specific target amount (Future Value) over a period. | Anuity payments – sinking factor (Savings) – How much to save to reach 1 mil USD? |
| Present Value of an Annuity (PVA) | Calculates the initial lump sum required today to allow for a series of equal, regular withdrawals (annuity) over a period at a given interest rate. | |
| Loan Payment / Annuity Payment | Calculates the regular debt repayment (annuity) required to repay a loan of a given principal at a specified interest rate and term. | Loan Payment and Amortization Schedule |
| Day Count Conventions | Explains the standards for counting days between two dates for financial instruments like bonds or complex loans. Main categories include the 30/360 and Actual/Actual (ACT) methods. | Day Count Conventions, Accrued Interest, and Yield |
Most Used Excel Finance Math Functions
| Description | Excel Function ENG | Article Link |
| Annuity Payment (Loan/Investment) | PMT | Loan Payment |
| Internal Rate of Return (IRR) | IRR | Internal Rate of Return |
| Number of Periods | NPER | NPER (Number of Periods) |
| Interest Rate Calculation | RATE | RATE (Interest Rate) |
| Effective Annual Rate (EAR) | Custom or **FV | Effective Interest Rate in Excel |
| Future Value | FV | FV (Future Value) |
| Present Value | PV | PV (Present Value) |
| Net Present Value (NPV) | NPV |