How to Use Excel to Calculate How Much a Loan Will Cost
"Microsoft Excel can make calculating the total cost of a loan much easier than trying to calculate values one step at a time. In addition, you can format a spreadsheet in Excel to allow you to see how changing one or more of the variables affects the total cost of the loan. When determining the total cost of the loan, you need to know the amount you are borrowing, the interest rate and how long it will take to repay the loan.
Format your spreadsheet. In cell A1, write ""Amount Borrowed."" In cell A2 write ""Interest Rate."" In cell A3 write ""Term (Years)."" In cell A4, write ""Monthly Payment."" In cell A5, write ""Total Cost.""
Enter the appropriate values next to the first three cells. For example, if you are borrowing $120,000 at a 5 percent interest rate over 30 years, you would enter ""120000"" in B1, ""0.05"" in cell B2 and ""30"" in cell B3.
Enter ""=PMT(A2/12,A3*12,A1)"" into cell B4. This will calculate the monthly payment on your loan. The interest rate is divided by 12 to find the monthly interest rate and the term is multiplied by 12 to determine how many monthly payments you will make.
Enter ""=B4_B3_12"" into cell B5 to calculate the total cost of the loan by multiplying the number of monthly payments times the cost of each monthly payment.
Tip Change the interest rate, amount borrowed or term values to see how a small change could affect both your monthly payment and your total cost of the loan.
Warning Make sure you enter the interest rate as a decimal. For example, 5 percent should be entered as ""0.05"" not ""5.""
Please support us in writing articles like this by sharing this post
Share this post to your Facebook, Twitter, Blog, or any social media site. In this way, we will be motivated to write articles you like.
--- NOTICE ---
If you want to use this article or any of the content of this website, please credit our website (www.affordablecebu.com) and mention the source link (URL) of the content, images, videos or other media of our website.
"How to Use Excel to Calculate How Much a Loan Will Cost"
was written by Mary
under the News
category. It has been read 194
times and generated 0
comments. The article was created on 29 January 2022
and updated on 29 January 2022