Introduction

Welcome! Loan & Investment Calculators Guide

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

Welcome to Excel Expert Level 6. In this course we will cover a range of advanced financial functions in Microsoft Excel, starting with key financial terms and commonly used functions. We will build calculators for loans, investments, interest rates, millionaire goals, initial deposits, home value, and loan amortization schedules, as well as review how to analyze credit card payments. We will also discuss how the course fits into the broader sequence of Excel lessons and ways to get help through the student forums. This course is designed for users who have completed earlier beginner and expert Excel courses.

Navigation

Prev: X06
Next: Lesson 01

Keywords

TechHelp Excel, Excel financial functions, loan calculator, investment calculator, interest rate calculator, millionaire calculator, home value calculator, loan amortization schedule, credit card payoff, PV function, Excel expert level

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Excel 2010 Expert Level 6 brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.

This course is all about using different financial functions in Microsoft Excel. We will begin by learning all the popular financial terms and the functions that go with them. We will learn how to build a loan calculator and an investment calculator. We will learn how to calculate interest rate. We will build something I like to call a millionaire calculator, which is how long will it take you to become a millionaire. We will make a home value calculator and we will learn how to build a loan amortization schedule.

This course was developed with Excel 2010. However, most of the functions used in this class are available in previous versions of Excel. This is an expert level class for Microsoft Excel. You should have at least taken my beginner classes 1 through 5, and preferably my other expert level classes 1 through 5 as well before taking this course.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. The beginner courses are for novice users with little or no experience with Excel. The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. The expert class goes into a lot more depth about each topic than the beginner classes did, and we will cover more functions, features, tips, and so on.

When you have mastered the expert classes, move up to the advanced lessons. You will learn how to build macros, build user forms, create your own templates, and many more advanced features that not everyone will use. But they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer series is designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Office applications.

Each of my series is broken down into different levels. For example, the beginner series contains five different levels, which you should have taken previously. This is the sixth level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels. When you finish all the expert classes, move up to the advanced series and finally the developer series.

Now, let's take a more detailed look at exactly what we are going to learn in today's class.

In lesson one, we will learn about some of Excel's financial terms and functions.

In lesson two, we are going to build a loan calculator. We will figure out the monthly payment on a home mortgage.

In lesson three, we will build an investment calculator. We will figure out if you deposit so much money into an investment, earning a certain interest rate, how much it will be worth after a year, two years, ten years, and so on.

In lesson four, we will build an interest rate calculator. You know the amount that you are investing. You know the amount you are getting back and you want to see if it was a good investment. What is your return on investment? What was your annual percentage rate that you received from your investment?

In lesson five, we are going to build something I like to call the millionaire calculator. You start off with an initial deposit. You are going to deposit so much money in the bank every month. How long will it take me to reach my goal of a million dollars?

In lesson six, we will calculate the initial deposit in an account if we know the current balance, the interest rate, and how many years the account has been open.

In lesson seven, we will create a home value calculator. If you know how much money you can afford to pay for a house every month, the bank has told you what your interest rate is going to be, and you want a 30-year mortgage, we will use the PV function to calculate how much house you can afford when you go house shopping.

In lesson eight, we are going to build a loan amortization schedule. This is where you can see a breakdown of each month's payments, including interest and principal paid.

In lesson nine, we will take a look at your credit card statement. We will see how long it will take to pay off that credit card balance if you just make the minimum payments every month. We will make a chart to determine what your monthly payment would have to be to pay off that credit card in two months, six months, twelve months, and so on.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel Interactive Student forums. If you are watching this course using my custom video player software or online in my web theater, you should see the student forum for each lesson appear in a small window next to the class videos if you have an active internet connection. Here, you will see all the questions that the other students have asked as well as my responses to them and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. Feel free to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting excellearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same spreadsheet that I make in the video. Build the spreadsheet with me step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum. Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Quiz

Q1. What is the primary focus of this expert level Excel course?
A. Using different financial functions and calculations in Excel
B. Building macros and user forms in Excel
C. Learning basic data entry in Excel
D. Learning how to create charts and graphs in Excel

Q2. Which of the following is NOT mentioned as a financial calculator created in this course?
A. Mortgage calculator
B. Millionaire calculator
C. Investment calculator
D. Depreciation calculator

Q3. What is the purpose of the lesson that covers the PV function?
A. To calculate the present value of a home you can afford
B. To plot stock market trends
C. To determine the depreciation of assets
D. To compare mortgage lenders

Q4. The loan amortization schedule in lesson eight helps you to:
A. Track monthly payment breakdown between interest and principal
B. Predict stock earnings
C. Create a home inventory
D. Calculate sales tax

Q5. According to the instructor, what should you do before trying to apply the lessons to your own projects?
A. Build the sample spreadsheet step by step with the lesson
B. Skip to the advanced sections
C. Memorize all Excel functions
D. Take a break for a week

Q6. How should students use the student forums mentioned by the instructor?
A. To ask questions and read answers from other students and the instructor
B. To download add-ons for Excel
C. To compare Excel versions
D. To submit final exams

Q7. The "millionaire calculator" helps you determine:
A. How long it will take to reach a financial goal with monthly deposits
B. When to retire
C. The best investments in the stock market
D. The value of your car after depreciation

Q8. What is the recommended way to learn from this course?
A. Watch the video once, then replay and follow along by creating the spreadsheet yourself
B. Only read the transcript
C. Search for answers online rather than watch the videos
D. Only do the final project

Q9. Which student group is the expert series designed for?
A. More experienced users already comfortable with Excel
B. Professional developers only
C. Complete beginners with no experience
D. Only financial analysts

Q10. What does the interest rate calculator lesson help you determine?
A. The return on investment or annual percentage rate
B. The optimal time to buy stocks
C. How to budget for a vacation
D. Future inflation rates

Answers: 1-A; 2-D; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 Excel Learning Zone is focused on mastering financial functions in Microsoft Excel. My goal here is to give you a solid understanding of common financial terms and how to use the built-in Excel functions to work with loans, investments, interest rates, and more. Everything I go over was created in Excel 2010, but you will find that almost all of the functions and examples apply to previous versions as well.

Because this is an expert level class, you should already be comfortable using Excel. Ideally, you have already worked through my beginner classes 1 through 5 and have also completed the first five expert courses. My curriculum is organized into progressive levels: beginner, expert, advanced, and developer. Beginners start with the fundamentals. The expert series, like this one, goes further with detailed coverage of complex topics and advanced features. After you finish the expert levels, you can move on to advanced lessons which cover macros, user forms, custom templates, and other features that will give your spreadsheets a professional edge. My developer lessons will walk you through programming with Visual Basic for Applications in Excel, showing you how to automate Excel and connect it with other Office applications.

Each level in my courses is designed to build on what you learned previously. This is the sixth level of the expert series, and by completing it, you will be prepared for the advanced and developer topics.

Here is what you can expect to learn today:

First, we will go over some of the key financial terms you'll encounter when working with Excel's financial functions.

Next, we will create a loan calculator to figure out monthly payments for a home mortgage.

Then, we will build an investment calculator. Using this tool, we'll see how much your investment will be worth over different periods, given a specific interest rate.

After that, you will see how to create an interest rate calculator. Suppose you know both what you invested and what you earned. We will calculate your return on investment and determine your annual percentage rate.

One of the more interesting calculators we will create is the millionaire calculator. With this tool, you can find out how long it will take you to reach a goal of one million dollars if you start with a certain amount and make regular monthly deposits.

We will also work out how to calculate the original deposit in an account if you know the current balance, the interest rate, and how many years the account has been open.

Afterward, we will make a home value calculator. If you have a target monthly payment in mind, and you know the interest rate and loan term, we will use the PV function to determine how much house you can afford to buy.

We will also prepare a loan amortization schedule. This will show you a detailed breakdown of each month's payments, including how much goes toward interest and how much goes to principal.

Finally, we will look at how to analyze your credit card statement. We will calculate how long it takes to pay off a credit card if you are making just the minimum payments, and we will make a chart that shows what your monthly payment would need to be to clear that debt in various timeframes.

If you get stuck or want to discuss any of the topics in this course, feel free to participate in the Excel Interactive Student forums. If you are using my custom video player or watching online, you will see a small window next to each lesson with the forum, where you can read and respond to questions from other students, see my replies, or post your own. If you are using another method, you can still visit the forums later at excellearningzone.com/forums.

I recommend that you watch each lesson from beginning to end the first time without trying to work along with me. Once you understand the concepts, play it back and build the sample spreadsheet as I do in the example. Working through the process yourself is the best way to truly learn the material. You should avoid using what you learn in your own work until you feel comfortable with the examples from the lesson.

If anything is unclear, go back and watch the lesson again, or post a question in the student forums. The most important thing is to have patience and an open mind. Excel can seem complex at first, but after some practice you will find it to be a very manageable and logical tool.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Excel financial terms and functions
Building a loan calculator
Calculating monthly mortgage payments
Building an investment calculator
Projecting investment growth over time
Calculating interest rate from investment data
Calculating return on investment
Calculating annual percentage rate
Building a millionaire calculator
Calculating time to reach a savings goal
Calculating initial deposit required for a balance
Creating a home value calculator
Determining house affordability with the PV function
Building a loan amortization schedule
Breaking down monthly loan payments by interest and principal
Analyzing credit card statement payoff time
Calculating credit card payments for different payoff periods
Creating charts for credit card payoff scenarios

Article

Welcome to this expert-level tutorial focused on mastering financial functions in Microsoft Excel. The goal here is to help you understand key financial concepts, apply popular Excel functions, and build practical calculators that are useful in real-life financial scenarios. Whether you are trying to analyze personal finances, plan for investments, or manage loans, these techniques will be valuable tools in your Excel skillset.

We start by learning about commonly used financial terms and the Excel functions that go hand in hand. Some of the most important Excel financial functions include PMT, PV, FV, RATE, and NPER. These help calculate payments, future and present values, interest rates, and the number of periods for investments and loans.

One of the first calculators to master is the loan calculator. Imagine you are looking to buy a house and need to determine the monthly payment for a mortgage. With Excel, the PMT function lets you do this quickly. For example, if your loan amount is 200,000 dollars, your annual interest rate is 5 percent, and your mortgage term is 30 years, you can calculate the monthly payment. You would enter the formula =PMT(0.05/12, 360, -200000). This breaks down to an interest rate per month (annual rate divided by 12), the total number of payments (years multiplied by 12), and the present value (the loan amount, entered as a negative number because it is money you owe). This gives you the monthly payment you would need to make.

Next, we explore building an investment calculator. Suppose you have a certain amount of money to invest, say 10,000 dollars, and want to know how much it will be worth after a specified number of years at a particular interest rate. The future value of an investment can be calculated with the FV function. For instance, if you invest 10,000 dollars for 10 years at an annual interest rate of 6 percent, compounded monthly, and you are not making any additional monthly contributions, you would use =FV(0.06/12, 120, 0, -10000). Here, the parameters are the monthly rate, number of months, the repeating payment per period (0 if you are not adding extra), and the negative of your initial investment.

If you want to find out the interest rate you are actually getting on an investment or a loan, Excel provides the RATE function. For example, let's say you invest 5,000 dollars and after 3 years you get back 6,000 dollars. You want to know what annual rate of return this represents. Assuming the investment compounds yearly and there are no additional payments, use =RATE(3, 0, -5000, 6000). To convert the result to a percentage, multiply it by 100.

Sometimes it is fun and insightful to figure out how long it would take to reach a certain savings goal. For example, the so-called "millionaire calculator" uses Excel to estimate how long it will take to amass one million dollars. If you make a fixed deposit every month and earn a certain interest rate, the NPER function can tell you the exact number of periods required to reach your goal. For instance, if you start with 10,000 dollars, deposit 500 dollars every month, and earn a 7 percent annual rate compounded monthly, the formula would be =NPER(0.07/12, -500, -10000, 1000000). This will tell you the number of months needed to reach one million dollars.

If you are working backwards and need to know how much you must have deposited initially to reach a current balance at a given interest rate over a certain number of years, you can use the PV (Present Value) function. Say you know your account now has 25,000 dollars, the average annual interest rate was 4 percent, and it has been 5 years with no additional deposits. The formula would be =PV(0.04, 5, 0, -25000). This will give you the original deposit amount.

A common real-life scenario is figuring out the price of a home you can afford given a specific budget for monthly payments, current interest rates, and a target mortgage term. The PV function helps here as well. Suppose you can pay 1,200 dollars per month, your interest rate is 4 percent annually, and you are aiming for a 30-year loan. Use the formula =PV(0.04/12, 360, -1200, 0) to find the maximum loan amount you can afford, which translates to your affordable house price.

One powerful financial tool is the loan amortization schedule. This shows how each payment for a loan is split between interest and principal and how the balance is reduced over time. To create this in Excel, set up columns for each payment period, calculate the interest for each period as the current balance multiplied by the periodic rate, the principal for each period as the payment minus the interest, and reduce your running balance accordingly. Use functions like IPMT and PPMT to help automate these calculations for each payment period.

Credit card debt is another area where Excel can provide insight. You can use Excel to project how long it will take to pay off a credit card if you only make the minimum monthly payment. Alternatively, you can set up a table that shows what payments you would need to make to clear the debt in 2 months, 6 months, 12 months, and so on. This lets you easily see the total interest that would be paid over the life of the balance under different scenarios.

Throughout your studies and practice, take advantage of Excel's built-in help, especially for these financial functions, as understanding the arguments for each function is crucial. Start by experimenting with the examples above, building each calculator in a new spreadsheet. Follow the process step by step, input your own scenarios, and tweak the values to see how the results change.

If you get stuck or have questions about the use of these financial functions, do not hesitate to search for solutions or discuss with others in online forums. Taking the time to build working examples and reviewing their outputs is the best way to learn these concepts thoroughly. With practice, you will find these techniques easy to use and extremely helpful in planning, analyzing, and managing your financial information in Excel.