Introduction

Welcome! PV, FV, RATE, NPER, PMT & Loan Tools

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 30. In this course we will continue the Comprehensive Function Guide to Microsoft Access by focusing on financial functions such as PV, FV, RATE, NPER, PMT, principal payment, and interest payment. We will discuss how to calculate monthly loan payments, future value of investments, rate of return, time to reach savings goals, required initial deposits, and house affordability. We will also talk about loan amortization and the difference between principal and interest in payments. This course builds on prior beginner and expert levels and uses Microsoft Access 2013 for demonstrations.

Navigation

Prev: X30
Next: Lesson 01

Keywords

Access Expert, financial functions, PV, FV, RATE, NPER, PMT, loan calculator, investment calculator, mortgage payment, future value, rate of return, become a millionaire, compound interest, initial deposit, college fund, house affordability, amortization

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 30 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is part 6 of my Comprehensive Function Guide to Microsoft Access.

Part 1 covered the string and logical functions. That was Access Expert Level 25.

Part 2 was the math and type conversion functions.

Parts 3 and 4 covered the date and time functions.

Part 6, that's today's class, covers the financial functions: PV, FV, RATE, NPER, PMT, principal payment, and interest payment.

In today's class, we are going to use these different financial functions to answer all kinds of questions that people commonly have about their finances.

For example, the most popular one: what is my monthly loan payment? If you have a mortgage or a car loan, you know you have to pay X amount of dollars over so many years at this percent interest rate. What is your monthly payment going to be?

You can calculate the future value of an investment. You are going to invest so much money each month at a certain interest rate. How much money will you have in your account after 5 years, 10 years, and so on?

What was my investment's rate of return? You put $10,000 into a business, and 5 years later you sold it for $20,000. How much of a rate of return did you get?

How long until I am a millionaire? That is one of my favorite calculations. If you know how much money you are putting into an investment, whether it is a savings account, a stock, or a bond, and you know what your rate of return is going to be, how long until that turns into a million dollars or any other dollar amount you want?

How much of a deposit do I have to make now to reach a goal amount in the future? Suppose you are saving for your kids, for their college fund. You know you need $50,000, and that is going to be 15 years from now. How much money do you have to invest now and in payments each month to reach that goal?

How much house can you afford? If you know how much you can afford each month for a monthly payment, what the bank interest rate is right now, and you want a 30-year mortgage, when you go house shopping, how much of a house can you afford? Can you afford a $300,000 house?

Finally, we will look at something called loan amortization, which is knowing what your monthly payment is, and how much of that payment is interest versus principal. That is sometimes very handy to know.

Before taking this class, I strongly recommend that you have finished my beginner series one through nine and the expert series classes, expert levels one through 29.

This class was recorded using Microsoft Access 2013, although most of the functions should work just fine in 2007 and 2010. I do not know if they all work in 2003, although I suspect they will. I believe all of these financial functions have been in Access for a very long time.

Unfortunately, I do not still have a copy of 2003 running on any of my machines. I know I probably should keep it, but I do not. So if any of you discover something that does not work in 2003, let me know.

In lesson one, we are going to have a brief overview of the different financial functions in Access and we are going to learn about interest rates. We will learn about compound and simple interest rates.

In lesson two, we are going to build a loan calculator. We are going to learn how to calculate a mortgage or other loan payment with the PMT function, where we specify the amount of the loan, the interest rate, and the number of years, and then Access will calculate our monthly payment.

In lesson three, we are going to build an investment calculator. This is where you can specify an amount you are willing to invest, the APR percentage rate you are getting, the number of years you are going to leave that money in the bank, and then Access will calculate the future value. You can see what it will be worth in two years, ten years, twenty years, and so on.

In lesson four, we are going to build an interest rate calculator. This is handy if you have an investment and you know what you have gotten back out of the investment and how long you had the investment for, and you want to figure out what your rate of return was. What was your annual percentage rate?

In lesson five, we are going to build a millionaire calculator. In other words, how long will it take you to become a millionaire? We know how much money we are going to put into our savings account every month. We know what our goal amount is, whether it is 500,000 or a million or whatever. We know what our interest rate is going to be. We will use the NPER function to calculate how many years it will take for us to reach our goal.

In lesson six, we are going to build an initial deposit calculator. In other words, I know my current balance, how much is in the account or how much I want to end up with, the interest rate that I received, and how many years I want to have the investment account open. I want to figure out how much of an initial deposit I need to hit that goal.

For example, a child's college fund. You know you want $50,000 at the end of 18 years and you know you are going to get a 5 and 3 quarters percent interest rate. According to our calculations, we need to put about $18,000 into an account up front. So that is what we will do in this lesson.

In lesson seven, we are going to build a house value calculator. You know how much you can afford for your monthly payment. You know what the bank is going to give you as an interest rate. You know you want a 30-year mortgage. When you go house shopping, how much of a house can you afford?

In lesson eight, we are going to build a simple loan amortization schedule. This is where you know your monthly payment, which we derived from the PMT function. Now I want to know, out of that monthly payment, how much of it each month is interest versus principal.

Quiz

Q1. Which financial function in Access is used to calculate your monthly loan payment?
A. PMT
B. FV
C. NPV
D. NPER

Q2. What can the FV function in Access be used to calculate?
A. The future value of an investment
B. The interest rate of a loan
C. The present value of a loan
D. The loan amortization schedule

Q3. If you invest $10,000 and sell it for $20,000 after 5 years, which function helps determine your rate of return?
A. RATE
B. PMT
C. PV
D. FV

Q4. To figure out how long it will take to reach a specific investment goal, such as becoming a millionaire, which function do you use?
A. NPER
B. FV
C. PMT
D. RATE

Q5. Which Access function helps you figure out how much of an initial deposit is needed to reach a target amount in the future?
A. PV
B. PMT
C. FV
D. RATE

Q6. What is an amortization schedule used for?
A. Breaking down each monthly loan payment into principal and interest components
B. Calculating the annual interest rate
C. Projecting the future value of an investment
D. Determining the initial deposit for a savings goal

Q7. In the house value calculator, if you know your monthly payment, interest rate, and loan duration, what does the calculation help you determine?
A. The maximum house price you can afford
B. The rate of return on your investment
C. The number of years for loan repayment
D. The future value of your house

Q8. Which lesson covers building an investment calculator to forecast future account balances?
A. Lesson three
B. Lesson eight
C. Lesson two
D. Lesson seven

Q9. Simple and compound interest rates are discussed in which lesson?
A. Lesson one
B. Lesson four
C. Lesson five
D. Lesson six

Q10. Which prerequisite is strongly recommended before taking this financial functions class?
A. Beginner series 1-9 and Expert levels 1-29
B. Only the beginner series
C. No prerequisites are recommended
D. Just knowledge of Excel functions

Q11. What version of Access was used to record this class?
A. Access 2013
B. Access 2003
C. Access 2019
D. Access 365

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on financial functions in Microsoft Access. I am Richard Rost, and this session is Expert Level 30, which is part six in my Comprehensive Function Guide series for Access.

If you are following along with this series, you may remember that I covered string and logical functions in part one (Expert Level 25). I then moved on to math and type conversion functions, followed by two parts covering date and time functions.

Now, in part six, I am going to show you how to use Access's financial functions. These include PV, FV, RATE, NPER, PMT, and ways to figure out both principal and interest payments.

We will be using these functions to answer some of the most frequently asked financial questions people have. For example, we will see how to solve for a monthly loan payment. If you have a mortgage or auto loan, you have an amount, length of loan, and interest rate. Using Access, you can figure out what that payment is going to be every month.

We will also look at how to work out the future value of an investment. If you invest a set amount each month at a certain rate, how much will you have in 5, 10, or 20 years?

Another question we will answer is how to determine the rate of return on an investment. Maybe you put $10,000 into something, and five years later, ended up with $20,000. We will use Access to figure out what your annual return was.

A common goal for many people is to become a millionaire. We will see how to calculate how long it will take to reach a specific financial goal, like a million dollars, given steady savings and a known rate of return.

We will also see how much you would need to deposit now in order to meet a future goal. For instance, if you are saving for a child's college fund and you know you will need $50,000 in 15 years, we will see how to compute the necessary up-front investment and recurring payments.

You will also learn how to decide what size house you can afford. If you know what you can pay each month, the current interest rate, and the typical term (like a 30-year mortgage), Access can help you figure out the maximum house price you can handle.

Lastly, we will explore loan amortization. We will see not just what your monthly payment is, but also how much of each payment goes toward interest and how much goes toward paying off the principal balance.

Before tackling this class, you should have completed my Beginner series (levels 1 through 9) and the Expert series up through level 29.

This course was recorded with Access 2013, but most of the functions discussed will work in Access 2007 and 2010. I expect most or all will also work in Access 2003, since these financial functions have been part of Access for quite some time. I no longer have Access 2003 installed, but if you try something and find it does not work in that version, please let me know.

For the specific lessons in this class:

Lesson one starts with a brief overview of the available financial functions and discusses the basics of interest rates, covering both simple and compound interest.

In lesson two, we will build a loan calculator using the PMT function. By entering the loan amount, interest rate, and term, Access will calculate your monthly payment.

Lesson three focuses on creating an investment calculator. You will be able to specify how much you are investing, the annual percentage rate, and time frame, and then Access will provide the projected future value for various time periods.

In lesson four, we will tackle the challenge of determining your actual rate of return from an investment. If you know what you invested, how long it was invested, and how much you got back, this lesson will show you how to figure out the effective annual rate.

Lesson five turns attention to the millionaire calculation. If you save a certain amount every month and aim for a specific financial target, Access and the NPER function can compute how many years it will take to meet that goal.

Lesson six will show you how to create an initial deposit calculator. For example, if you want to fund a college account and have a specific goal in mind, knowing the interest rate and time frame, you will be able to calculate the required starting deposit.

Lesson seven helps with house shopping. By entering your maximum affordable monthly payment, the current interest rate, and term length, you can estimate the value of the house you can buy.

Finally, in lesson eight, we will build a simple loan amortization schedule. With your monthly payment in hand, you will be able to see how much of each payment is interest versus how much is paying down the principal.

If you want to see detailed, step-by-step instructions for everything covered here, you can watch the complete video tutorial on my website at the link below. Live long and prosper, my friends.

Topic List

Overview of financial functions in Access
Understanding interest rates
Compound vs. simple interest
Calculating loan payments with PMT
Building a loan calculator
Future value calculations with FV
Building an investment calculator
Calculating rate of return with RATE
Building an interest rate calculator
Calculating years to reach a financial goal with NPER
Building a millionaire calculator
Calculating initial deposit for future goals with PV
Building an initial deposit calculator
Calculating affordable house value
Building a house value calculator
Creating a simple loan amortization schedule
Calculating interest vs. principal in loan payments

Article

If you want to take control of your finances using Microsoft Access, the program offers a set of built-in financial functions that can help you answer a wide range of questions, from calculating your monthly loan payment to determining how long it will take to reach your investment goals. In this guide, we will explore how you can leverage these financial functions in Access to make informed decisions about loans, investments, and more.

Let us start with the basics. Suppose you have taken out a loan or a mortgage and you need to figure out what your monthly payment will be. Maybe you are about to finance a car, buy a house, or want to understand the cost of an existing loan. Access provides the PMT function for this purpose. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

For example, if you borrow $100,000 at a 5 percent annual interest rate for 30 years, you can use the PMT function to figure out what your monthly payment would be. The syntax is as follows:

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

The rate is the interest rate per period. If your interest rate is 5 percent annually and you pay monthly, you would divide 0.05 by 12 to get the monthly rate. The nper argument is the total number of payments. For a 30 year loan, paying monthly, this would be 30 times 12, or 360. The pv argument is the present value, meaning the principal of the loan. Since you are borrowing money, use a negative value. So, PMT(0.05/12, 360, -100000) would give you your monthly payment.

When it comes to saving and investments, you might want to know how much your money will grow over time. Access provides the FV function, which stands for future value. This is useful for determining how much an investment made today will be worth in the future, given regular contributions and an interest rate.

Suppose you plan to deposit $200 each month into an account that pays 6 percent annual interest, and you keep saving for 10 years. The FV function would look like this:

FV(rate, nper, pmt, [pv], [type])

Again, the rate would be 0.06/12 for monthly, nper would be 10 times 12 or 120, pmt is the payment each period, so -200 (negative because you are paying out), and pv is zero if you are starting from nothing. So, FV(0.06/12, 120, -200, 0) gives you the amount you will have after 10 years.

Sometimes you know how much you invested and what you got out of it, and you want to find out the rate of return. That is where the RATE function comes in. The RATE function calculates the interest rate per period, given the number of periods, payment, present value, and future value. For example, if you invested $10,000 and got back $20,000 five years later with no extra contributions, you could use RATE to determine your annual rate of return. Since you are treating the investment outflow as negative and inflow as positive, the function would look like RATE(nper, pmt, pv, fv). Here, nper is 5, pmt is 0, pv is -10000, and fv is 20000. So, RATE(5, 0, -10000, 20000) returns your annual interest rate.

Another common question is how long it will take to reach a financial goal. Maybe you are saving a set amount each month and want to know how many months or years it will take for your savings to reach a certain target. The NPER function is designed for this. It calculates the number of periods for an investment based on constant periodic payments and a constant interest rate.

For example, if you want to know how long it will take to save $1,000,000 by depositing $500 each month at a 7 percent annual interest rate, the formula would be NPER(0.07/12, -500, 0, 1000000). This calculates the number of months needed to reach a million dollars, which you can then convert to years if you like.

Sometimes you know how much you need in the future and want to figure out how much you need to invest now. The PV function calculates the present value, or the lump sum you would need to deposit today to reach a certain future value given an interest rate and periods. If you know you want $50,000 in 18 years, and the annual interest rate is 5.75 percent, you would calculate PV(0.0575, 18, 0, 50000) to get the present value you need to invest now.

When it comes to making big financial decisions like buying a house, you will also want to know how much home you can actually afford. If you know how much you can afford to pay each month, the interest rate, and the number of years for your mortgage, you can use the PV function again. In this case, you use your payment amount as PMT (negative, because it is an outflow). For instance, if you can pay $1,500 per month at 4.5 percent interest over 30 years: PV(0.045/12, 360, -1500, 0) gives you the loan amount the bank would offer, which tells you your affordable house price.

Another valuable calculation is understanding loan amortization. Each payment you make on a loan is divided between paying interest and reducing your principal balance. Knowing how much of each payment goes toward interest versus principal is key for budgeting and understanding your loan costs over time. Access provides the PPMT and IPMT functions for this purpose. PPMT returns the principal paid for a given period and IPMT returns the interest paid for that period.

For example, suppose you have a $100,000 loan at 6 percent interest, 30 years, and you want to know how much of your first payment is principal versus interest. The PPMT function would look like this:

PPMT(rate, per, nper, pv)

So, for the first payment: PPMT(0.06/12, 1, 360, -100000)

Similarly, for interest, you use the IPMT function:

IPMT(0.06/12, 1, 360, -100000)

This way, you can build an amortization schedule for your loan, showing the breakdown of each payment over time.

All of these functions work in queries or VBA code within Access. If you want to automate these calculations, you can use VBA code like this:

Dim Payment As Double
Payment = PMT(0.05/12, 360, -100000)

This calculates the monthly payment for a $100,000 loan over 30 years at 5 percent interest. You can then output this value to a form, report, or message box as needed.

To sum up, Microsoft Access has robust built-in tools for working with financial data. Whether you want to calculate loan payments, future values of investments, rates of return, periods needed to reach goals, or analyze amortization schedules, these functions will help. By mastering these calculations, you will be better equipped to make wise financial decisions, plan for the future, and answer important questions about your money easily and accurately within Access.