Excel Finance Loan Schedule: How to Create Loan Schedule Form with Payment Function in Excel Finance @WLH Education Myanmar

Payment function is described as PMT() in the Microsoft Excel and as in payment which calculates the constant monthly payment that is necessary to extinguish a loan in n periods.

Let say, a person who is borrowing money from someone about 500,000. Then as a pay back he will need to completely pay back the money in 10 years at the interest rate of 3%.

This situation can be calculated using PMT() function in the excel. In order to calculate the PMT() function for monthly, we can do as follows.

• First calculates the monthly interest rate by dividing the annual interest rate by 12

• In the output cell, write the PMT() function and placed the amount of loan, the number of periods (months) and the monthly interest rate.

• The resulted monthly payment will be the total amount that includes the principal and the interest payment to fully pay back the loan.

However, this is the equal amount of the monthly payment for the complete loan. This does not show the amount of debt in remaining. In order to know which month left for how much money, the remaining debt or residual debt can be calculated based on the principal.

Calculate the Residual Debt or Remaining Debt (For the First Period)

• To calculate the Residual debt, the formula is the Residual Debt (Entire loan if first time) – Principal

• Therefore, the principal should be first calculated.

• According to the formula of the payment, Payment = Principal + Interest. In which the Payment is already known but not for the payment. If we can get the Interest in here, we can calculate the principal.

• Calculating the Interest Payment, the formula is = the Residual debt (entire loan if first time) * Monthly Interest Rate

• After getting the Interest, Principal can be calculated by subtracting the Interest from the Monthly Payment.

• Having completed the principal, we can calculate the Residual Debt for the first time. For the first period only, there is no residual debt from previous therefore the entire loan will act as the Residual Debt.

• Therefore, residual debt = Entire Loan – Principal in this month

Calculate the Residual Debt or Remaining Debt (For second to end period)

• For second to end period, the Interest Payment = Residual Debt from the previous * Monthly Interest Rate. In this way, the interest payment can be calculated.

• After that, the principal can calculate by subtracting the Interest payment from the Monthly Payment the same as the first period.

• Then for the Residual Debt from the second to end period, Residual Debt = Previous Month Residual Debt – Principal for this month.

• In this way, the residual debt can be computed till the end of the period.

As the number of periods approaching to the end of the period, the Interest Payment is falling down while the principal is going up with the residual debt left zero.

In this way, we can calculate the remaining debt within the loan schedule.

Learn More About Microsoft Office - https://www.office.com/

Learn More About Microsoft Excel - https://www.microsoft.com/en-gb/microsoft-365/excel

Learn More About Microsoft 365 - https://www.microsoft.com/en-gb/microsoft-365/microsoft-office

Microsoft Excel Support - https://support.microsoft.com/en-us/office/

Uploads from Excel Accounting and Financial Analysis@WLH Education

How to NPV & Discounting Cash Flow (DCF) in Accounting and Finance Analysis (Excel) Myanmar

https://youtu.be/DwI5v2dz68w

How to learn “ABSOLUTE REFERENCES $$” in Microsoft Excel in Myanmar Language Ep. 5

https://youtu.be/8md0Ex7DQB0

Excel Finance IRR: How to Calculate Internal Rate of Return (IRR) for Profit Excel Finance Myanmar

https://youtu.be/P5yLPG2Qtn8

Follow Us on Facebook:

https://www.facebook.com/WaiLinHtetUSYD/

WLH Education Channel တွင်တင်ဆက်ပြသနေသော အစီအစဉ်များ

https://www.youtube.com/channel/UCZu4G6gPMucHDwqPvtzUKkQ/playlists?

အစီအစဉ် - ၁။ Microsoft Word Basic to Advance Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JUeYO0HUQQYL9SOasIQ-uid?sub_confirmation=1

အစီအစဉ် - ၂။ Microsoft Excel Basic to Advance Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXkX-Ows6CBGIoCUsz8N2an?sub_confirmation=1

အစီအစဉ် - ၃။ R-Programming for Data Science Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JV7Zdl4P_hFuTQmpROSnkq1?sub_confirmation=1

အစီအစဉ် - ၄။ Advanced Microsoft Excel (FUNCTIONS)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JUCun-bJU3v1xaTxKnXaWdS?sub_confirmation=1

အစီအစဉ် - ၅။ Finance and Accounting Analyst (Excel)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXva2nZc6KH5e4yDZvTQrxl?sub_confirmation=1

အစီအစဉ် - ၆။ Build Functions in R (Series 1 - 7)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXlg7Uy4019Hg5xWBV7xt5u

Excel Finance Loan Schedule: How to Create Loan Schedule Form with Payment Function in Excel Finance @WLH Education MyanmarPayment function is described as PMT() in the Microsoft Excel and as in payment which calculates the constant monthly payment that is necessary to extinguish a loan in n periods.Let say, a person who is borrowing money from someone about 500,000. Then as a pay back he will need to completely pay back the money in 10 years at the interest rate of 3%.This situation can be calculated using PMT() function in the excel. In order to calculate the PMT() function for monthly, we can do as follows.• First calculates the monthly interest rate by dividing the annual interest rate by 12• In the output cell, write the PMT() function and placed the amount of loan, the number of periods (months) and the monthly interest rate.• The resulted monthly payment will be the total amount that includes the principal and the interest payment to fully pay back the loan.However, this is the equal amount of the monthly payment for the complete loan. This does not show the amount of debt in remaining. In order to know which month left for how much money, the remaining debt or residual debt can be calculated based on the principal.Calculate the Residual Debt or Remaining Debt (For the First Period)• To calculate the Residual debt, the formula is the Residual Debt (Entire loan if first time) – Principal• Therefore, the principal should be first calculated.• According to the formula of the payment, Payment = Principal + Interest. In which the Payment is already known but not for the payment. If we can get the Interest in here, we can calculate the principal.• Calculating the Interest Payment, the formula is = the Residual debt (entire loan if first time) * Monthly Interest Rate• After getting the Interest, Principal can be calculated by subtracting the Interest from the Monthly Payment.• Having completed the principal, we can calculate the Residual Debt for the first time. For the first period only, there is no residual debt from previous therefore the entire loan will act as the Residual Debt.• Therefore, residual debt = Entire Loan – Principal in this monthCalculate the Residual Debt or Remaining Debt (For second to end period)• For second to end period, the Interest Payment = Residual Debt from the previous * Monthly Interest Rate. In this way, the interest payment can be calculated.• After that, the principal can calculate by subtracting the Interest payment from the Monthly Payment the same as the first period. • Then for the Residual Debt from the second to end period, Residual Debt = Previous Month Residual Debt – Principal for this month.• In this way, the residual debt can be computed till the end of the period. As the number of periods approaching to the end of the period, the Interest Payment is falling down while the principal is going up with the residual debt left zero.In this way, we can calculate the remaining debt within the loan schedule.Learn More About Microsoft Office - https://www.office.com/Learn More About Microsoft Excel - https://www.microsoft.com/en-gb/microsoft-365/excelLearn More About Microsoft 365 - https://www.microsoft.com/en-gb/microsoft-365/microsoft-officeMicrosoft Excel Support - https://support.microsoft.com/en-us/office/Uploads from Excel Accounting and Financial Analysis@WLH Education How to NPV & Discounting Cash Flow (DCF) in Accounting and Finance Analysis (Excel) Myanmarhttps://youtu.be/DwI5v2dz68wHow to learn “ABSOLUTE REFERENCES $$” in Microsoft Excel in Myanmar Language Ep. 5https://youtu.be/8md0Ex7DQB0Excel Finance IRR: How to Calculate Internal Rate of Return (IRR) for Profit Excel Finance Myanmarhttps://youtu.be/P5yLPG2Qtn8Follow Us on Facebook:https://www.facebook.com/WaiLinHtetUSYD/WLH Education Channel တွင်တင်ဆက်ပြသနေသော အစီအစဉ်များhttps://www.youtube.com/channel/UCZu4G6gPMucHDwqPvtzUKkQ/playlists?အစီအစဉ် - ၁။ Microsoft Word Basic to Advance Coursehttps://www.youtube.com/playlist?list=PL0d0T6i8c-JUeYO0HUQQYL9SOasIQ-uid?sub_confirmation=1အစီအစဉ် - ၂။ Microsoft Excel Basic to Advance Coursehttps://www.youtube.com/playlist?list=PL0d0T6i8c-JXkX-Ows6CBGIoCUsz8N2an?sub_confirmation=1အစီအစဉ် - ၃။ R-Programming for Data Science Coursehttps://www.youtube.com/playlist?list=PL0d0T6i8c-JV7Zdl4P_hFuTQmpROSnkq1?sub_confirmation=1အစီအစဉ် - ၄။ Advanced Microsoft Excel (FUNCTIONS)https://www.youtube.com/playlist?list=PL0d0T6i8c-JUCun-bJU3v1xaTxKnXaWdS?sub_confirmation=1အစီအစဉ် - ၅။ Finance and Accounting Analyst (Excel)https://www.youtube.com/playlist?list=PL0d0T6i8c-JXva2nZc6KH5e4yDZvTQrxl?sub_confirmation=1အစီအစဉ် - ၆။ Build Functions in R (Series 1 - 7)https://www.youtube.com/playlist?list=PL0d0T6i8c-JXlg7Uy4019Hg5xWBV7xt5u

int abs functions i m ms excel expert tutorial day 23 finance guy nishantexcel finance trick 10 interest rate for pay day loansdeep finance use of excel linest function cogs revenue regressionfinance function in ms excel ms excel tips tricksbest finance courses 2021 stock market advance excel basics of finance free certificategoal seek i m ms excel expert tutorial day 25 finance guy nishantsocial security retirement a finance perspective using excelpurchase finance equipment 8360 excel accounting problem 2021count formula i m ms excel expert tutorial day 11 finance guy nishantexcel finance loan schedule how to create loan schedule form with payment function in excel financeexcel finance functions pmt pv rate nper in urdu hindilearn islamic finance with excel sheets english halaal finance interest free finance excel100 most important advanced excel tricks mainly for finance audit and tax departmentartificial neural network for finance designed in ms excelexcel formulas learn more than 100 for mis finance in hindi by sttexcel finance irr how to calculate internal rate of return irr for profit excel finance myanmarexcel accounting software excel bookkeeping software excel bill software excel finance software3 hindi ms excel monthly budget diy understanding public personal finance receipts and expensesutsa business finance excel application 4 cash handling problem video 1 of 3how to use finance formula in excel intrate ipmt ispmt in excel financial formula series lesson 9finance group a excel teaching madam mahfuzah salimutsa business finance excel application 2 time value of money problem 1 future valueql 5 finance excel simple vs compound interest benzifinance using excel sheet mgt232 lecture in hindi urdu 32excel personal finance compound interest vs simple interest and investment riskcreate a monthly budget in excel part 1 personal finance tipshow to create a finance calculator on microsoft excel to help manage your business or organizationarya the heroies excel finance analyst microsoft excel building professional chart in excel 2calculate variance in finance with excelexcel for finance creating subtotalsarya the heroies excel finance analyst microsoft excel building professional chart in exceludemy free courses learn html javascript microsoft excel and finance learningutsa business finance excel application 10 value at riskgoogle finance nifty banknifty excel sheet is readytop 5 financial function in excel in hindifinancial statement ratio analysis in excel 3 in 1 learn finance fundamentalsexcelexperts com excel for finance tips what is a basis pointcalculating beta on excel using historical data from yahoo financeburst your sap finance reports into excelyahoo finance exporting data to excel 2021pv nper rate pmt fv basic excel finance functions corporate finance 4 8excel add in for yahoo finance part3excel finance class 107 calculating beta 2 methods chart beta slope correl stdev functionsbasics of excel modelingyahoo finance exporting data to excel 2022utsa business finance excel application 2 time value of money problem 2 present valuelink yahoo finance stock data to excel worksheetbudgeting 101 personal finance spreadsheetautosum in excel in urdu by adreescara membuat laporan keuangan di excel dengan cepatfundamentaldaten finance yahoo com excel 2010 makrotuto excel leçon 3 7 les fonctions financières vpm hdthe use of spreadsheet in computing databasic excel functions afsomaliinvestment analysis solar panel engineering economic analysis explained using ms excelhow to calculate cost of equity through capm approach in excel spread sheetexcel pmt function house or car loan payment excel one minute quick referencehow to use ms excel sum subtraction multiplication division in online teaching in gujarati languageexcel tutorial for seafarers bank records income and expenses part 1exp19 excel ch04 hoeassessment investments excel chapter 4 hands on exercise assessment investment

Excel Finance Loan Schedule: How to Create Loan Schedule Form with Payment Function in Excel Finance @WLH Education Myanmar

Payment function is described as PMT() in the Microsoft Excel and as in payment which calculates the constant monthly payment that is necessary to extinguish a loan in n periods.

Let say, a person who is borrowing money from someone about 500,000. Then as a pay back he will need to completely pay back the money in 10 years at the interest rate of 3%.

This situation can be calculated using PMT() function in the excel. In order to calculate the PMT() function for monthly, we can do as follows.

• First calculates the monthly interest rate by dividing the annual interest rate by 12

• In the output cell, write the PMT() function and placed the amount of loan, the number of periods (months) and the monthly interest rate.

• The resulted monthly payment will be the total amount that includes the principal and the interest payment to fully pay back the loan.

However, this is the equal amount of the monthly payment for the complete loan. This does not show the amount of debt in remaining. In order to know which month left for how much money, the remaining debt or residual debt can be calculated based on the principal.

Calculate the Residual Debt or Remaining Debt (For the First Period)

• To calculate the Residual debt, the formula is the Residual Debt (Entire loan if first time) – Principal

• Therefore, the principal should be first calculated.

• According to the formula of the payment, Payment = Principal + Interest. In which the Payment is already known but not for the payment. If we can get the Interest in here, we can calculate the principal.

• Calculating the Interest Payment, the formula is = the Residual debt (entire loan if first time) * Monthly Interest Rate

• After getting the Interest, Principal can be calculated by subtracting the Interest from the Monthly Payment.

• Having completed the principal, we can calculate the Residual Debt for the first time. For the first period only, there is no residual debt from previous therefore the entire loan will act as the Residual Debt.

• Therefore, residual debt = Entire Loan – Principal in this month

Calculate the Residual Debt or Remaining Debt (For second to end period)

• For second to end period, the Interest Payment = Residual Debt from the previous * Monthly Interest Rate. In this way, the interest payment can be calculated.

• After that, the principal can calculate by subtracting the Interest payment from the Monthly Payment the same as the first period.

• Then for the Residual Debt from the second to end period, Residual Debt = Previous Month Residual Debt – Principal for this month.

• In this way, the residual debt can be computed till the end of the period.

As the number of periods approaching to the end of the period, the Interest Payment is falling down while the principal is going up with the residual debt left zero.

In this way, we can calculate the remaining debt within the loan schedule.

Learn More About Microsoft Office - https://www.office.com/

Learn More About Microsoft Excel - https://www.microsoft.com/en-gb/microsoft-365/excel

Learn More About Microsoft 365 - https://www.microsoft.com/en-gb/microsoft-365/microsoft-office

Microsoft Excel Support - https://support.microsoft.com/en-us/office/

Uploads from Excel Accounting and Financial Analysis@WLH Education

How to NPV & Discounting Cash Flow (DCF) in Accounting and Finance Analysis (Excel) Myanmar

https://youtu.be/DwI5v2dz68w

How to learn “ABSOLUTE REFERENCES $$” in Microsoft Excel in Myanmar Language Ep. 5

https://youtu.be/8md0Ex7DQB0

Excel Finance IRR: How to Calculate Internal Rate of Return (IRR) for Profit Excel Finance Myanmar

https://youtu.be/P5yLPG2Qtn8

Follow Us on Facebook:

https://www.facebook.com/WaiLinHtetUSYD/

WLH Education Channel တွင်တင်ဆက်ပြသနေသော အစီအစဉ်များ

https://www.youtube.com/channel/UCZu4G6gPMucHDwqPvtzUKkQ/playlists?

အစီအစဉ် - ၁။ Microsoft Word Basic to Advance Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JUeYO0HUQQYL9SOasIQ-uid?sub_confirmation=1

အစီအစဉ် - ၂။ Microsoft Excel Basic to Advance Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXkX-Ows6CBGIoCUsz8N2an?sub_confirmation=1

အစီအစဉ် - ၃။ R-Programming for Data Science Course

https://www.youtube.com/playlist?list=PL0d0T6i8c-JV7Zdl4P_hFuTQmpROSnkq1?sub_confirmation=1

အစီအစဉ် - ၄။ Advanced Microsoft Excel (FUNCTIONS)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JUCun-bJU3v1xaTxKnXaWdS?sub_confirmation=1

အစီအစဉ် - ၅။ Finance and Accounting Analyst (Excel)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXva2nZc6KH5e4yDZvTQrxl?sub_confirmation=1

အစီအစဉ် - ၆။ Build Functions in R (Series 1 - 7)

https://www.youtube.com/playlist?list=PL0d0T6i8c-JXlg7Uy4019Hg5xWBV7xt5u