Easy Intro to Financial Math in Excel - Pt 1 Home Mortgage example

Easy Intro to Financial Math in Excel - Pt 1 Home Mortgage example. 《Maths for Business》09 - Mortgage

Easy Intro to Financial Math in Excel - Pt 1 Home Mortgage example video duration 34 Minute(s) 1 Second(s), published by A2 Excel, Stats and Finance on 15 07 2018 - 14:00:03.

Get the file here: https://s3.amazonaws.com/a2excel.com/Blog/Files/FinMathEng.xlsx In this video we will learn the basic financial formulas in Excel
We will This short video gives a quick tip for how to get an idea of the possible Principle & Interest payment (not including taxes or insurance) for a home your are .

Wondering how you can calculate mortgage payments while looking at homes? This episode Aaron discusses the simple math you can use to quickly figure out What is the math formula for mortgage payments? This video is designed to help mortgage loan officers learn the right way to calculate the mortgage math

Get the file here: https://s3.amazonaws.com/a2excel.com/Blog/Files/FinMathEng.xlsx

In this video we will learn the basic financial formulas in Excel. We will simulate a simple mortgage loan to apply them all, and in the next video we will see if renting the apartment, we are going to buy leaves enough return to justify the loan. The financial formulas in Excel we’ll be learning will be: PMT (payment), rate, IPMT (interest payment), Present Value (PV) and future value (VF).

We will start by evaluating the loans as the bank proposes. We pass all the data that the bank passes to our Excel format, including the down payment, the amount borrowed and the interest rate (CAT), as well as the term of 15 years and inflation.

Next, we start doing the calculations that Excel requires. For our financial formulas to work, Excel requires that the rate be on a monthly basis because the payments are monthly. You have to divide the rate by 12 and take the number of periods in months. Once this is done, we can use the PAYMENT formula in Excel to calculate the monthly fixed payment that we will make to the mortgage. For this reason, it will be important to calculate monthly inflation.

Once this is done, we can begin to put together our financial run, where we will calculate when interest and capital payments are paid, as well as the total amount to be paid off the mortgage. We will use Excel to understand how our interest payment decreases each month (due to the unpaid balances), while our amount to be paid increases. All this basing us on the fixed payment that we obtained with the formula PAYMENT of Excel.

Once the amortization table has been assembled, it is necessary to verify that the future value is zero. We can add up all the total payments and verify that we are effectively paying more interest than capital in the life of the 15-year loan. However, we must take into account the effect of inflation on all cash flows that arise.

We are going to use the formula of current value (VA) in Excel to discount the effect of inflation on financial flows, and we will verify that effectively the payments are decreasing in real terms every month. In the end it turns out that the bank will charge us, in real terms, one third of the current value of the house.

We will finish by reviewing other calculations. We will use the payment formula in Excel to calculate the amount paid to interest in a specific month of our financial run. Then we will use the formula of TASA to obtain how much the bank charges us effectively. And finally we will be nper to verify that the number of periods is 180.

To finish, the future value of our loan will be calculated using Excel's VF formula to obtain the final value of the mortgage at the end of the term.