Cost of Borrowing Revealed

When it comes to understanding the true cost of borrowing, we can’t afford to rely solely on advertised rates. Terms like APR (Annual Percentage Rate) and APY (Annual Percentage Yield) might seem interchangeable, but they tell very different stories due to compounding. With Excel’s built-in financial functions like NOMINAL, EFFECT, PMT, and RATE, we can decode these terms and calculate the real financial impact. In this post, we’ll walk through practical scenarios that highlight these differences and how to model them in Excel step-by-step.

Video

Step-by-Step: Understanding Interest Rates in Excel

Lenders and investment institutions often present interest rates in the best light possible. As Excel users, we gain an edge by translating these advertised rates into effective costs or yields using accurate models. That way, we’re never misled by fine print. Let’s dive into the key functions and see how they help us translate between nominal, effective, and periodic interest rates.

Types of Rates

• Nominal Rate / APR (Annual Percentage Rate)
  • Where you’ll see it: Consumer and business loans (mortgages, car loans, credit-card offers, HELOCs).
  • Why lenders use it: It excludes compounding, so the quoted rate looks smaller and easier to compare at a glance.
• Effective Annual Rate / APY (Annual Percentage Yield)
  • Where you’ll see it: Deposit products (CDs, high-yield savings, money-market accounts).
  • Why banks use it: It includes compounding, so the yield looks larger and highlights the true growth of your deposit.
• Periodic Rate (nominal rate ÷ number of compounding periods)
  • Where you’ll see it: Rarely advertised; mostly lives inside amortization schedules and Excel functions such as PMT, FV, RATE.
  • Why it exists: Required for month-by-month (or day-by-day) calculations for example, the monthly rate used to generate your loan payment or daily rate used to credit interest to your CD.

Conversion Function Summary

Excel has several interest rate functions, and in this post, we are going to get into the details of the following:

• NOMINAL
  • Returns: the nominal (APR) that corresponds to a given effective (APY) rate and compounding frequency.
  • When to use: you’re handed an advertised APY (e.g., on a CD) but need the nominal rate so you can divide by 12, 365, etc., and feed the resulting periodic rate into PMT, FV, and other time-value-of-money functions.
• EFFECT
  • Returns: the effective annual rate (APY) that results from a nominal APR and its compounding frequency.
  • When to use: you have APRs from multiple loans or deposits with different compounding schedules and want a true apples-to-apples yield or borrowing cost.
• RATE
  • Returns: the periodic interest rate that balances a series of cash flows; multiply by 12 (or 365, etc.) for an annual nominal rate if needed.
  • When to use: you know the payment, term, and principal on a loan (or the contribution, horizon, and goal for savings) and need to solve for the underlying rate.

Quick Cheat-sheet

• Need Nominal (APR) from an advertised APY? → NOMINAL
• Need APY to compare offers that give APRs? → EFFECT
• Need the underlying rate itself from payments or goals? → RATE (then scale/convert as required)

Now let’s dig deeper into these functions and their arguments.

Convert Between Effective and Nominal Rates

Let’s start by distinguishing between the nominal rate (APR) and the effective rate (APY). APR is the stated rate, while the APY accounts for compounding frequency. Excel gives us two functions to work with:

• =NOMINAL(effect_rate, npery) – Converts an APY to its equivalent APR.
• =EFFECT(nominal_rate, npery) – Converts an APR to its equivalent APY.

Let’s start with the NOMINAL function.

The NOMINAL function returns the nominal (stated) annual interest rate that corresponds to a given effective annual rate and compounding frequency.

NOMINAL(effect_rate, npery)

• effect_rate – required; the effective annual rate (APY) you want to convert.
• npery – required; number of compounding periods per year (e.g., 12 for monthly, 365 for daily).

Suppose we encounter a 3.5% APY on a CD compounded daily. To find the nominal rate:

=NOMINAL(0.035, 365)

This returns 3.44%, the nominal rate that reflects annual value without compounding. To double-check the math, we can confirm with the EFFECT function.

The EFFECT function returns the effective annual interest rate (APY) of a given nominal annual rate and a specified compounding frequency.

EFFECT(nominal_rate, npery)

• nominal_rate – required; the nominal (stated) annual rate (APR) to be converted.
• npery – required; number of compounding periods per year (e.g., 12 = monthly, 4 = quarterly, 365 = daily).

Let’s use the value returned from our prior example:

=EFFECT(0.0344, 365)

It brings us back to the original 3.5%. These two functions are direct inverses of each other which is perfect for quick comparisons or validations.

Use Nominal Rates in Other Financial Functions

Now that we’ve converted the rate, we can use it in functions like FV (future value), PMT (payment), and RATE (interest rate). But remember, these functions require periodic rates. That means we must divide the nominal rate by the number of periods per year.

Example: Estimate future savings using a high-yield savings account:

• Monthly deposit: $300
• Years: 10
• APY: 5.12%, compounded daily

First, convert the APY to a nominal rate:

=NOMINAL(0.0512, 365)

Then use the nominal rate with the FV function:

=FV(NOMINAL(0.0512, 365)/12, 10*12, -300, 0)

This gives an approximate future value of $46,569. Excel assumes compounding matches the payment period.

Determine How Much to Save to Meet a Goal

Let’s say our goal is to have $100,000 in 10 years. With a 5.12% APY (daily compounding), how much should we save every month?

=PMT(NOMINAL(0.0512,365)/12, 10*12, 0, 100000)

This returns a monthly savings amount of approximately $644.

Compare Loan Offers Using the EFFECT Function

If we’re borrowing money, the key is to find the true annual cost. Let’s say we’re comparing several loan options with varying APRs and compounding periods:

Loan Type	APR	Compounding
Credit Union HELOC	10.00%	Daily (365 periods)
Online Personal Loan	10.10%	Quarterly (4 periods)

To compute the effective interest rate for each, we use:

=EFFECT(APR, compounding_periods)

Despite the higher nominal rate, the quarterly-compounded loan may actually be cheaper overall and Excel cab help us see that clearly.

Solve for Unknown Interest Rate

The RATE function returns the periodic interest rate that balances a series of equal payments and cash flows over a specified number of periods (multiply by 12, 4, etc., to express it as an annual rate if desired).

RATE(nper, pmt, pv, [fv], [type], [guess])

• nper – required; total number of payment periods (e.g., months or years).
• pmt – required; payment made each period (constant amount; expressed as a cash outflow, so typically negative).
• pv – required; present value or principal amount (cash inflow, usually positive for a loan).
• fv – optional; desired future value or remaining balance after the last payment (default = 0).
• type – optional; timing of payments: 0 = end of period (default), 1 = beginning of period.
• guess – optional; initial estimate of the rate to help the iterative calculation converge (default = 0.10 or 10 %).

Let’s say we have this loan data:

• Amount: $20,000
• Monthly payment: $377.42
• Term: 5 years
• Unknown interest rate

=RATE(5*12, -377.42, 20000)*12

This formula returns the annual rate of 5%, assuming monthly compounding. If we include a balloon payment or future value, we just modify the formula to add a FV argument.

Calculate Loan Payments or Savings Amounts

Often, interest rates are used to compute loan payments or savings amounts. We can use the PMT function to do these computations.

The PMT function returns the constant periodic payment needed to amortize a loan (or accumulate savings) given a fixed interest rate and term.

PMT(rate, nper, pv, [fv], [type])

• rate – required; interest rate per period (e.g., monthly rate if payments are monthly).
• nper – required; total number of payment periods.
• pv – required; present value: initial loan principal (positive) or current balance (negative for savings).
• fv – optional; desired future value or remaining balance after the last payment (default = 0).
• type – optional; payment timing: 0 = end of period (default), 1 = beginning of period.

Let’s do a couple examples.

Loan Payments with Balloon Payment:

If we want to know the monthly payment of a loan

• Amount: $20,000
• Rate: 5%
• Term: 5 years

=PMT(.05/12, 5*12, 20000)

This returns our monthly payment amount of $377.42.

Now, instead of making a monthly payment to pay off a loan, what if we invested it for ourselves to reach a savings goal?

Savings Goals:

If we want to save $1,000,000 in 30 years assuming an overall 10% average annual return, and start with 0 today in our savings account, we can figure out the monthly payment using the formula below:

=PMT(0.10/12, 30*12, 0, -1000000)

We find that we’d need to save around $442.38 per month to hit our goal. Pretty motivating!

Download Sample Excel File

To help apply these concepts right away, download the accompanying Excel file that includes all the exercises and formulas covered above.

Summary

By mastering Excel’s financial functions, we uncover the hidden costs of borrowing and get realistic projections for investing or saving. Understanding the difference between nominal and effective rates allows us to:

• Translate APY to APR and vice versa
• Calculate monthly loan payments
• Estimate future values from savings
• Reverse-engineer required savings amounts
• Fairly compare lending offers using compounding insights

And the best part? Excel handles the heavy math (assuming we set it up correctly lol)!

FAQs

What’s the difference between APR and APY?

APR is the nominal (stated) annual rate, whereas APY includes compounding and reflects the actual earnings or cost over time.

Which Excel function converts APY to APR?

Use =NOMINAL(effect_rate, npery) to convert APY to nominal APR.

How can I compare loan offers with different compounding terms?

Use the EFFECT function to calculate effective annual interest and compare apples to apples.

Can I estimate how much to save monthly for a specific goal?

Yes, use the PMT function to determine the required monthly contribution for a given future goal and expected return.

What does the RATE function do?

RATE calculates the interest rate needed to achieve a goal with known payments, periods, and amounts.

Why does my future value appear as a negative number?

Can I use these formulas for savings and loans alike?

Absolutely. Functions like PMT and FV work for any financial scenario, just adjust the inputs for saving or borrowing.

Do I really have to match time units in formulas?

Yes! Inconsistent time units (e.g., annual rate with monthly periods) will produce incorrect results every time.