Loan Amount $ Annual Interest Rate Life Loan (in years) Number of Payments per Year Total Number of Payments Payment per Period Sum of Payments Interest Cost

Your Loan Amount value is the total amount you owe. Your Annual Interest Rate value is the percentage of interest that accrues each year. Your Life Loan value is the amount of time you have in years to pay off the loan. Your Number of Payments per Year value is how many times you make a payment in one year. Your Total Number of Payments value is the Life Loan value multiplied by the Payments Per Year value. Your Payment per Period value is the amount you pay per payment. Your Sum of Payments value covers the total cost of the loan. Your Interest Cost value determines the total cost of the interest over the course of the Life Loan value.

For example, if you make a payment a month on a 30-year life loan, you would type in “360” here.

For the provided screenshot, the formula is “-PMT(B6/B8,B9,B5,0)”. If your values are slightly different, input them with the appropriate cell numbers. The reason you can put a minus sign in front of PMT is because PMT returns the amount to be deducted from the amount owed.

For example, if you make 360 payments of $600. 00, your total cost of the loan would be $216. 000.

Date - The date the payment in question is made. Payment (number) - The payment number out of your total number of payments (e. g. , “1”, “6”, etc. ). Payment ($) - The total amount paid. Interest - The amount of the total paid that is interest. Principal - The amount of the total paid that is not interest (e. g. , loan payment). Extra Payment - The dollar amount of any extra payments you make. Loan - The amount of your loan that remains after a payment.

Select the first entry in your Payment (Number) column. Drag your cursor down until you’ve highlighted to the number that applies to the number of payments you’ll make (for example, 360). Since you’re starting at “0”, you’d drag down to the “362” row. Click Fill in the top right corner of the Excel page. Select Series. Make sure “Linear” is checked under the “Type” section (when you do your Date column, “Date” should be checked). Click OK.

You must preface this formula with the “=IF” tag to complete the calculations. Your “Annual Interest Rate”, “Number of Payments per Year”, and “Payment per Period” values will need to be written like so: $letter$number. For example: $B$6 Given the screenshots here, the formula would look like this: “=IF($B$10<K8+(K8*($B$6/$B$8)),$B$10,K8+(K8*($B$6/$B$8)))” (sans the quotation marks).

In order to apply this formula to all subsequent cells in this column, you’ll need to use the “Fill” feature you used earlier.

This formula must be prefaced with a “=” sign in order to work. In the screenshots provided, the formula would look like this: “=K8*$B$6/$B$8” (without quotation marks).

In order to apply this formula to all subsequent cells in this column, you’ll need to use the “Fill” feature you used earlier.

For example, if your “Interest” cell is H8 and your “Payment ($)” cell is G8, you’d enter “=G8 - H8” without the quotations.

In order to apply this formula to all subsequent cells in this column, you’ll need to use the “Fill” feature you used earlier.

For the screenshots provided, you’d type “=K8-I8-J8” without the quotations.

In order to apply this formula to all subsequent cells in this column, you’ll need to use the “Fill” feature you used earlier.

Your principal should match up exactly with the original loan amount. Your payments should match the total cost of the loan from the mortgage calculator. Your interest should match the interest cost from the mortgage calculator.