How do I calculate the present balance on a mortgage?

I am trying to calculate the unpaid balance of a mortgage for a given payment period, using a spreadsheet function.





For example, on a loan of 拢200,000 with an interest rate of 5.4% and a repayment period of 25 years, how could I calculate the amount to be repaid in the 24th month of the loan?





I wish to use standard spreadsheet functionality to calculate this.|||I have MS Excel 2003 which doesn't have this specific function wizard built in. It has a template for mortgage amortization tables which can be downloaded. However, you can create your own formula if you know what the monthly payment of the mortgage is. Create 5 columns; payment date, payment amt, interest pmt, principal pmt, and loan balance. For the first payment date create the row like this: In the interest column, your formula is 200000*.05/360*30. In the principal column your formula is payment amt - interest amt (the previous column). The loan balance column is 200000 - principal pmt. For the second payment date (and all rows after) use the same formula except for the loan balance. That formula would be the previous loan balance (the cell just above) - the principal pmt.


Copy the second row of formulas for the entire length of the loan (each row is one month).





It's much easier to show this on a spreadsheet than it is to write it out like this. Basically, mortgage interest is calculated by taking the loan amount times the interest rate and dividing it by 360 (avg number of days in a year). This gives you the daily interest amount. Multiply that by 30 days (avg number of days in a month) to get the total interest for the month. Subtract the interest from the monthly payment amount to get the amount applied to the principal. Subtract this from the total loan balance to get the new loan balance. Use this new loan balance to calculate interest and principal for the next month, and so on and so on...|||I would just call the place where I got my loan and ask them. It would only take a minute.|||It all depends at what point you are in the 25 years of the mortgage. If you are at the start, you've paid nothing on the principal.





The way mortgages work is that in the early years, virtually everyting you pay goes toward interest. As the years progress, greater and greater percentages go toward principal; and by the last years, everything goes toward principle. The ratio of principal to interest slides through the history of the loan.





Your building society can give you a specific spread-sheet breakdown of the whole thing.