Become a whiz at Financial Loan calculations in Excel
5.0 (1 rating)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
288 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Become a whiz at Financial Loan calculations in Excel to your Wishlist.

Add to Wishlist

Become a whiz at Financial Loan calculations in Excel

Calculate your monthly loan payments and see exactly how much you can save by making additional payments.
5.0 (1 rating)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
288 students enrolled
Created by Brendan Dale
Last updated 4/2017
English
Current price: $10 Original price: $25 Discount: 60% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 40 mins on-demand video
  • 1 Article
  • 3 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Calculate monthly payments due for a loan
  • Calculate the interest portion being paid during each period of a loan and see how it changes over the course of the loan
  • Calculate the capital portion of the loan that is being paid each month and see how it changes over the course of the loan
  • Calculate the amount of money that can be saved by paying additional payments
View Curriculum
Requirements
  • You should already know how to use Excel at a basic level and know how to create basic functions
  • You will need Excel 2016 (prior versions will work but the screen may look different and this could cause confusion)
Description

Learn to calculate how much money you can save by making additional payments into your loan.

In this course we'll learn how to use the Financial Loan formulas in Excel to calculate how much money you can save when making additional payments into your loan.  We'll also learn to calculate the exact period at which you can pay your loan off.  This will really assist you to set achievable goals regarding your outstanding mortgage bond.

This course will take you through a step-by-step guide to create a spreadsheet which details the payment for each period of a loan over the lifetime of the loan.  In the example we look at a mortgage bond of 1,500,000 which is paid monthly over 20 years.

The course is created using Excel 2016 and although prior versions of Excel have all the same functionality, the screen may look different so if you are not comfortable using Excel you could get confused.  You really should have some basic knowledge of Excel already and you should be able to use formulas and understand how to link cells into formulas.

The topics we cover in the course are:

  • The Excel PMT function to calculate the monthly payment
  • The Excel IPMT function to calculate the interest portion of each months payment
  • The Excel PPMT function to calculate the principal (capital) portion of each months payment
  • The Excel IF function for logical conditions

It is very encouraging to calculate how much you can save on your bond with even small additional amounts!

Who is the target audience?
  • This course is for anyone who has a mortgage bond (home loan or similar long-term loan) and who wants to calculate how much money they can save by making additional payments.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
+
Welcome
1 Lecture 00:52

This introductory video gives you a chance to ready yourself for the course.  We simply look at what you can expect from the course.

Preview 00:52
+
The Financial Formulas you need to know
4 Lectures 10:03

This lecture covers the PMT function in Excel and by the end of it you will know how to calculate the payment due on a loan provided you know the loan amount, the number of periods and the interest rate.

Preview 04:40

This lecture covers the IPMT function in Excel and by the end of it you will know how to calculate the amount of interest paid during a given period of the loan.

Preview 01:57

In this lecture we will look at the PPMT function in Excel and by the end of this you will know how to calculate the amount that is paid towards the principal (or capital) of the loa for a given period.  Thus you will know by how much the loan is being reduced for the given period.

Preview 01:33

This lecture looks at the IF function in Excel.  This is a simple logic condition which you will learn to use.

Preview 01:53
+
Putting it all together
5 Lectures 27:11

In this lecture we will do the initial setup of the spreadsheet that we will be using for the rest of the course.  This includes some details of the loan as well setting up headings for the various columns.  We'll also create a row for each period of the loan.  Here you can follow exactly what I do and set up your own spreadsheet.

Preview 04:31

In this lecture we learn to use the PMT, IPMT & PPMT functions to calculate the payment for each period of the loan, as well as the interest portion and capital portion.  You will also calculate the outstanding balance of the loan for each period until it is paid off.  This will give you a detailed overview of your loan payments.

Calculate the loan payments over lifetime of loan
06:53

This lecture deals with the effects of additional payments on the loan and in this lecture you will learn to calculate the interest portion of a payment after additional payments have been made.

Cater for Additional Payments
06:15

In this lecture we will use the IF function to add some conditional statements to our calculations.  At the end of the lecture we will be able to calculate the exact amount of money that will save after making additional payments.  We will also be able to calculate the exact period at which our loan will be paid off.

Cater for Additional Payments - Part 2
07:12

In this lecture will create a graph showing the Outstanding Capital of the loan for each period; with and without additional payments.  This will give you a visual overview of your loan and help you see how additional payments really affect your loan term.

Add a graph to visualize your loan
02:20
+
Summary
2 Lectures 02:11

In this final lecture we simply recap on what we have learnt.  I really hope to inspire you to set goals for yourself to pay off your loan!  This spreadsheet gives you the ability to accurately work towards paying your loan off at a set date.

Recap what we did
01:49

This bonus lecture will help you to link into a group of like minded people who are interested in being in control of their finances and making wise financial decisions.

Bonus Lecture: Take Charge of your Money
00:22
About the Instructor
Brendan Dale
5.0 Average rating
1 Review
288 Students
1 Course
Software Developer and computer fundi

I'm a software developer with over 20 years of experience.  I live & work in Cape Town (South Africa) and work predominattly in the Financial Industry and generally create systems that manage pension & retirement fund members and their investments.  I love coding and finding solutions to problems and my Udemy courses reflect that with useful real-live examples.