This is part 6 of the Comprehensive Guide to Access Functions.
We will be learning about the Financial Functions in today's class. Even
if you don't have a job in banking or finance, you will still get a lot
out of these lessons if you ever have to deal with money (and most of us
do). We'll start out with a brief overview of the Access financial
functions, what the various terms mean, and how interest is calculated
(simple v. compound). We'll talk about annual percentage rates.

Next we will build a Loan Calculator,
and learn how to calculate the monthly payment for a loan,
whether it's a 30-year home mortgage, or a 5-year auto loan. We will
learn about the PMT function, and how to use the amount of the
loan, the interest rate, and the number of years to determine the
monthly (or any frequency) payment amount. For example, a $100,000 loan
at 5% APR interest over 30 years gives you a monthly payment of about
$536.

We will build an Investment Calculator
so we can calculate the future value of an investment with the FV
function. If we know the initial amount of the investment, the interest
rate, and the number of years we're planning on investing, the FV
function can calculate the future value - the value the investment will
have at that point in the future. For example, you want to put $1000 in
a savings account earning 3% interest. What will be in your account in
10 years?

Next we'll create an Interest Rate
Calculator. This will allow us to figure out the ROI (Return on
Investment) for any of our endeavors using the RATE function. For
example, you invested $10,000 in a friend's business 3 years ago, and he
wants to buy you out for $15,000 now. That turns out to be a return of
about 14%, so it's a good deal.

Next is one of my favorites, the
Millionaire Calculator. This uses the NPER function to how
long it will take your investment to reach a particular value. For
example, you're planning for retirement and you figure you're
going to need $100,000 to retire comfortably. You know you can easily
set aside $1,000 each month in a savings account earning 5% interest.
the NPER function will tell you that it will take you about 7 years to
reach your goal. You can also use it to see how long it will take you to
become a millionaire, given how much you're able to save each month.
It's fun. Try it.

We will create an Initial Deposit
Calculator. This one is good for determining how much you need to
put away now to reach a specific goal in the future. This is great for
determining how much to save for your kids' college funds. For
example, let's say you know that you're going to need $50,000 to put
your kids through school, and they're going to start college in 15
years. Your bank has a bond you can buy at a 5.75% APR. So, you can use
the PV function to figure out that you need to invest $18,277 now
to have that $50,000 in 15 years. The PV function can also calculate the
value if there are extra monthly payments thrown in - so you could start
with $15,000 and then add in $250 per month if you want.

Next is the House Value Calculator.
This is a great calculator to use when you want to figure out how
expensive of a house (or car, or boat, or whatever) you can buy. You
know how much you can afford for your monthly mortgage payment. You know
you want a 30 year mortgage. You know the bank is offering you a 7% APR
interest rate. You can use the PV function to determine that you
can afford a house value of $300,615.

Finally, we're going to spend some time
learning the basics of Loan Amortization. We already know how to
use the PMT function to calculate the monthly payment for a loan.
With the IPMT and PPMT functions we can calculate how much
of that payment is going towards interest and principal,
respectively. We can see what the principal balance of our loan is going
to be after any point in the payment schedule. So, for example, after
paying off your home loan for 15 years, you can see what your "payoff
amount" would be. I have a separate
Loan
Amortization Seminar which goes into much more specific
examples, but this is a great introduction to the concept for
non-financial professionals.

This is the 30th class in the Access Expert series. This
is the 6th class in my Comprehensive Function Guide series. If you're
serious about building quality databases with Access, and you have any
interest AT ALL in financial functions, don't miss out on
this course. Of
course, if you have any questions about whether or not this class is
for you, please contact me.

Complete Outline - Access Expert Level
30
00. Intro (6:00)
01. Functions & Interest (9:08)
Access Financial Funtions Functions Covered Today: PV, FV
RATE, NPER PMT, PPMT, IPMT Functions NOT Covered: NPV,
DDB, IRR, MIRR SLN, SYD Simple v. Compound Interest Rates
APR Annual Percentage Rate
02. Loan Calculator (18:08)
Calculate Mortgage Payments PMT Function Amount of Loan
Number of Periods Interest Rate Monthly Payment Total
Amount Paid Total Interest Paid Rounding Issues Extra
Payment at End of Loan
03. Investment Calculator (7:37)
Calculate Future Value of Investment Amount of Investment
APR Annual Percentage Rate Number of Years to Invest Extra
Payments Future Value FV Function
04. Interest Rate Calculator (11:30)
What was my rate of return ROI Intial Investment Amount
Amount of Return Number of Years to Invest What was my
Interest Rate RATE Function Why sometimes divide by 12
Annual vs. Monthly Compounding |
05. Millionaire Calculator (9:30)
How
long until I'm a millionaire
Initial Deposit Amount
Monthly Payment Amount
Goal
Amount
Interest Rate
Calculate Number of Months
Convert to Whole Years
INT
Function
NPER
FUnction
06. Initial Deposit Calculator (7:06)
College fund calculator
How
much deposit to hit goal return
Current Balance Amount
Interest Rate
Years
to have account open
Extra
annual payments
PV
Function
07. House Value Calculator (5:55)
Monthly payment you can afford
Interest rate from bank
How
many years for your mortgage
Calculate house you can afford
PV
Function
08. Loan Amortization (22:47)
How
Amortization Works
Interest and Principal Portions
Build
LoanF Form
Create
AmortT Table for Periods
Build
AmortF Subform
Link
Child and Master Fields
Calculate Interest Paid
IPMT
Function
Calcaulte Principal Paid
PPMT
Function
Rounding Issues
IIF
Function to Hide Errors
09. Review (6:51) |

|
Keywords:
Comprehensive Function Guide, Financial Functions, microsoft access tutorial, microsoft access tutorial, microsoft access training, PV, FV, RATE, NPER, PMT, PPMT, IPMT, Interest Rate, Mortgage Calculator, Investment, ROI, Millionarie, Deposit |