In order to calculate an affordable mortgage price, many mortgage lenders uses the guidelines which are very similar to the one another. Most of the lenders have fixed the debt to income ratio as 36% as maximum. This value is an absolute maximum in all cases. Although the maximum is 36% if you can keep your debt to income ratio in the range 28% to 33% chances for getting approval will increase slightly.

You can Estimate mortgage payment using the online mortgage calculator. Remove your all monthly debt payments from your income and multiply the remaining with the 0.28 (it is for the conservative things) and again with 0.33 (it’s for the rest of the things). This mortgage payment estimator gives you a rough figure and it is what your approximate monthly mortage payable amount. If you afford the high amount you are having good chances of getting approved for the mortgage loan programs.

Beside from these figures you need to consider the future things like your children education, retirement 401k fund etc. Although at present you are not paying for the retirement plans you may be required to pay those amount in future. So if you are planning to get committed with the long term 30 year fixed rate mortgage keep all the future things in mind.

Calculating mortgage payments is a bit complicated process.** **You have many ways to estimate mortgage payment. You can use Mortgage payment estimator online, spread sheet application and so on.

Here let’s see how to calculate or estimate the your mortgage payment using the spread sheet application

**Step 1**

Place the cursor in the top most cell in the spread sheet and enter the following information

- Required Loan Amount
- Number of periods
- Loan Interest Rate

**Step 2**

Try to number each and every row with the appropriate payment amount. If the amount is larger one you can consider using the “fill handle” option.

**Step 3**

Now it time to create some columns with the below information filled.

- Payment
- Initial loan balance
- Total amount which is applied for interest
- Total amount which is applied for principal
- Total remaining balance

**Step 4**

Now let’s see how to enter and use the formulas in excel.

**How to calculate the payment**

You can make use of the PMT function to estimate your monthly mortgage payment. Prior to that make sure you ware are having the following information on hand.

- Nper – indicates number of payments you need to make
- Rate – indicates the interest rate of the loan
- Pv- indicates the present value of the mortgage
- Fv- Indicates the future value of the mortgage
- Type – it’s a logical value. 1 depicts the payment need to be made at the starting of the tenure. 0 indicates no initial payment

**How to calculate the amount needs to applied for interest **

To calculate this you can make use of the IPMT function.

- Rate – indicates the interest rate of the loan
- N-period you wish to find out the interest ( starts from 1)
- Nper- indicates total number of payments.
- Pv – Indicates the present value
- Fv- indicates the future value
- Type – it’s a logical value used same in PMT function.

**Calculate amount applied to the principal**

Amount applied to the principal = payment – amount applied to the interest (output of IPMT function)

**Calculate remaining balance**

New balance = Total loan balance – principal paid

Repeat the above steps to get all the remaining payments.

As an alternative to use the excel you can make use of mortgage payment estimator available in the internet.