Michael, You'll never learn how if someone else does it for you, and these mortgage amortization sheets are an easy place to start. They're complicated enough to make you think, but not so much that you can't shoot your way out of a corner. Additionally, nobody thinks the same way you do, so if you learn how to build the sheet yourself, you'll be able to customize it to get exactly what you want.
Begin by collecting (and naming) your known values: Principal, annual interest rate, term of the loan in years, annual insurance, annual taxes. Put those in the upper left corner of the sheet, one on each row, and label them. E.g., A1=principal; B1 = principal amount; C1 = blank. A2 = Term (years); B2 = length of mortgage; C2 = B2*12 (the total term of the loan in months). A3 = Annual rate; B3= APR of loan; C3 = B3/12. You get the idea. Let column A be the label for the commonly-used terms of a loan. Column B is the actual value of those terms; and column C is the periodic value--the one you'll be using in formulas. You'll have an easier time keeping track of things if you name those periodic values using Insert->Names...Define. Name them something that makes sense to you. Now set up the columns for your amortization schedule right below the known values. You know the principal amount of your loan and the annual interest rate. Use the PMT formula to find the monthly payment amount: =PMT(number of years * 12; annual rate / 12; remaining principal). There are two more parameters that can be passed to the PMT function, but in this case both are zero and can be ignored. (See the function wizard and the help.) For the first payment, you'll use the principal of the loan as the starting value. For all subsequent payments, use the *remaining* principal. Once you have the monthly payment (which includes monthly interest), you calculate the interest portion by multiplying the principal that you used in the PMT formula times the monthly interest rate, like this: = (annual rate / 12) * principal Subtract the interest portion from the total payment, then subtract the remainder from the principal to get the remaining principal. Use that remaining principal as the "principal" in the next line's PMT calculation. You know your annual taxes and insurance, so simply divide those amounts by 12 to arrive at the monthly amounts. To find your total monthly payment, add the result of the PMT function to the monthly tax and insurance contributions. That should get you going. Write back when you get stuck. (And I'm sure you will. Always happens to me, at least.) Jim Plante > On Feb 27, 2015, at 2:12 PM, [email protected] wrote: > > As I am very green in this area I am searching for a program that I can use > to track PITI payments that I can set up to track my monthly payments. I will > know my loan amount, interest rate, annual insurance and annual property tax > liabilities. Inputting those variables I need to determine monthly payments. > As the payments are entered I should see a running total of escrow balances, > interest paid, principal paid and new loan amount. > > I know this is simple enough for most people to find, but these financial > spreadsheets are a little difficult for me to navigate so if I could be > pointed in the right direction of existing software for me to use, I would > really appreciate the help. > > Thank you in advance for your assistance. > Michael B. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
