Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Expert 29

Next: Access Expert 31 >

Access Expert Level 30

Expert Microsoft Access Tutorial - 1 Hour, 44 Minutes
 
 
Access Expert 30 is Part 6 of our Comprehensive Guide to Access Functions. Today's class focuses on Financial Functions. Even if you are not a financial professional, you will find the topics in today's class of interest. Anyone who has a home mortgage or an auto loan will benefit from these lessons. You will learn all of the popular financial functions, how simple and compound interest work, how to calculate a monthly loan payment, and lots more. Topics include:
 
access cd   - Financial Functions
  - Simple v. Compound Interest
  -
Loan Payment Calculator: PMT
  - Find Future Value of an Investment: FV
  - Calculate Return On Investment: RATE
  - Years Until I'm a Millionaire: NPER
  - How Much Home Can I Afford: PV
  - Saving For Kids College Fund Calculator
  - Loan Amortization Basics
  - Calculating Principal and Interest Parts

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 30
Description: Access Expert Level 30
Versions: Recorded with Access 2013. Most of the material should work with all versions of Access. The majority of the functions covered in today's class go all the way back to the first versions of Access.
Pre-Requisites: Access Expert Level 29 strongly recommended, as you should watch all of my classes in order. However, if you're skipping around, you should have at least completed the Beginner series and the first 3 or 4 levels of the Expert series where I start to cover functions.
Running Time: 1 Hour, 44 Minutes
Cost: $26.99


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.

financial functions interest

 

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.

monthly loan calculator PMT function

 

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?

investment calculator FV function

 

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.

interest rate calculator RATE function

 

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.

millionaire calculator NPER function

 

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.

initial deposit PV college fund

 

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.

house value calculator PV function

 

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.

loan amortization PPMT IPMT functions

 

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
 
 

Student Interaction: Microsoft Access Expert 30

Richard on 8/2/2015:  Microsoft Access Expert 30Access Expert 30 is Part 6 of our Comprehensive Guide to Access Functions. Today's class focuses on Financial Functions. Even if you are not a financial professional, you will find the topics in today's class of interest. Anyone who has a home mortgage or an auto loan will benefit from these lessons. You will learn all of the popular financial functions, how simple and compound interest work, how to calculate a monthly loan payment, and lots more. Topics include: - Financial Functions - Simple v. Compound Interest - Loan Payment Calculator: PMT - Find Future Value of an Investment: FV - Calculate Return On Investment: RATE - Years Until I'm a Millionaire: NPER - How Much Home Can I Afford: PV - Saving For Kids College Fund Calculator - Loan Amortization Basics - Calculating Principal and Interest Parts Click here for more information on Access Expert Level 30, including a course outline, sample videos, and more. This course was recorded using Access 2013, but most of the functions covered are valid for all versions of Access. This class follows Expert Level 29. The next class in the series is Expert Level 31.
Uriel R on 8/2/2015: Richard, I love your classes and how you teach, the only thing I can complaint about is the production schedule, class can't come fast enough. Hope at the end you teach how to build own functions!
Guillermo P on 9/10/2015: I am trying to find where to start the Lever 2 Access training and can not find it. Help?

Reply from Alex Hedley:

Guillermo what was the last course you took?

B2 or E2
Beginner Level 2
Expert Level 2

Rasulul Amin Murad on 9/23/2015: HI Brother Richard,
Thanks for your initiative to teach us the msaccess. I know very little about this program. Recently i am trying to built a invoice for my small company but the local ppl is asking huge amount of payment. That's why i search over google to get tutorial to make a Invoicing and Quotation Billing Application for my company. There i hv found you today. I really impressed to show ur video. I still dnt have any idea tht i will able to complete or not but i must try on it. Sorry for huge writing.. Thanks for the tutorials.. Allah bless you.

John Newton on 10/13/2015: Hello, I have a question regarding adding records to a sub-form. I have completed course 1-30 and all beginner courses.

Ex; I have tables, queries, forms and sub-forms;

Main tables; DriverT with Fields like: DriverID, Fname, Lname, etc.
CarT with field like: CarID, MakeIDFK, ModelIDFK, YearIDFK, CarName
A junction table; CarXDriverT
Supporting Tables; MakeT, ModelT, YearT

Queries; DriverXCarQ; with Fields: CarXDriverID, CarIDFK, DriverIDFK.

Forms: A main Form; CarF with the fields from the CarT and a subform; DriverXCarSubF
The subform get data from; DriverXCarQ

I want to be able to add a new Driver to the DriverT in the form the main form in the sub-form.

Any Ideas? Is this in an upcoming course? A past course?
Thanks it really kicking my butt.



Reply from Alex Hedley:

Have you read the outline for the Relationship Seminar?

Stephen Petersen on 12/30/2015: Can you provide a query example of how to use NPV. I am creating a database of potential capital projects, some of which have multiple years and I want to calculate the NVP of the capital cost and fuel ect when I have the staring values

Reply from Alex Hedley:

MS Article


Dim Fmt, Guess, RetRate, NetPVal, Msg
Static Values(5) As Double ' Set up array.
Fmt = "###,##0.00" ' Define money format.
Guess = .1 ' Guess starts at 10 percent.
RetRate = .0625 ' Set fixed internal rate.
Values(0) = -70000 ' Business start-up costs.
' Positive cash flows reflecting income
' for four successive years.
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
' Calculate net present value.
NetPVal = NPV(RetRate, Values())
Msg = "The net present value " & _
"of these cash flows is "
Msg = Msg & Format(NetPVal, Fmt) & "."
' Display net present value.
MsgBox Msg


Stephen P on 12/30/2015: This is still confusing to me. What would the NPV calculation look like if the initial investment was 10,000 and increased by 2.25% over the next 5 years?

Reply from Alex Hedley:

It's been a few years since I did anything with NPV at uni, I'll need to refresh my memory.
Have you calculated the PV for each year?


Stephen Petersen on 1/26/2016: How do I calculate NPV in a query? I have been calculating FV of year1, then year2, then year3 etc and than adding up the values. However, if the number of years is a variable, then it doesn't work.
Thomas Szypulinski on 2/25/2016: Hello Mr. Rost,
I like to begin with congratulating you on the great learning software and hope that it will continue to grow and cover many great subjects. English is my second language and with that I find your classes very user friendly and understandable. I have 2 questions that I couldn't find answers for on my own:
1. how can I download final full copy of the database you build in all beginner and expert classes?
2. do you have advanced classes ready for purchase?


Reply from Alex Hedley:

Student databases
Advanced Courses These are using 2003 but the principles and most of the code translates, just won't look the same

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP