Excel Formula For Mortgage Payment Resource

Chaminda replied: "=750,000 - CUMPRINC Principal paid=CUMPRINC (rate,nper,pv,start_period, end_period,type)"

amontef replied: "Hi. You need to use the PMT function, then calculate your interest and principle per month. Your monthly percentage times beginning balance should give you your interest. Subtract interest from monthly payment to figure your principle. Subtract principle from beginning balance to get your end balance. Your end balance now becomes next month's beginning balance. Do the same for each month to arrive at ending balance for first year. Hope that helps! Feel to email me if you need more assistance."

How do a write a formula for excel that will multiple a number by one of three possible percentages? I'm trying to write a formula for excel that will multiple a number by either 2.75% 2% or 1.75%. here's the scenario: purchase price X down payment Y mortgage M Depending on the mortgage loan to value the mortgage amount will be multiplied by one of the percentages. 80.01 - 85.00% 1.75% 85.01 - 90.00% 2.00% 90.01 - 95.00% 2.75% hope someone can help thanks

gp4rts replied: "In the cell you want the percentage, use a conditional function. That is found in the function list under "statistical" and has the form IF(logic,x,y) logic would be an expression like A1>B1, or D3=E6, where A1, B1, etc are cell references. If the statement is true, the value of the function is x, otherwise it is y. You can nest the statements for multiple conditions. For example if the value in C1 is to be tested against values in A1 and B1: IF(C1<=A1,x,(IF(C1>=B1,y,z)) This gives you x for C1 less than (or equal) A1, y for C1 more than (or equal) B1 and z if it is in between."

-- replied: "Here is the excel formula to use: Assuming cell A1 contains the mortage loan value. Enter the following formula into a different cell to calculate the mortage %. =IF(AND(A1>= 0.801,A1<= 0.85), 0.0175, IF(AND(A1>= 0.8501,A1<= 0.9),0.02, IF( AND(A1>= 0.901,A1<= 0.95),0.0275,0))) ** One word of caution: Since you did not state what the 'default' % is, I just assumed it is 0. So whenever the value at cell A1 is OUTSIDE the range of the values you gave above, it gives a zero *** To change the default value change the zero in the last IF statement in the formula into some other %. << Let me know if it works !! >> Edit: Azuma, Mortage (M) is how much you are borrowing, Downpayment (Y) is how much you paid for initially, and Purchase is how much the property cost. So M = X - Y"

freond1 replied: "Put the loan to value in a cell named lv and the mortgage amount in a cell name M formula has nested if's: =if(lv>.9,.0275,if(lv>85,.02), if(lv>.8,.175@@@)*M I split this onto two lines since Yahoo truncated it when it was all on one line. The @@@ can be blank, or an error condition statement, or a value like 0). This makes some assumptions about the loan to value and doesn't test for them. You can add such conditions by more if statements such as if(lv<.8001.... Hope this helps. I've created some really wild nested if's. You can do a lot with them."

Azuma Kazuma replied: "It would be best to describe this problem in an Excel worksheet since it would be somehow tedious to do so here. Anyway, here's my attempt (Assuming that my illustration below is the Excel worksheet): ____A______B______C___ 1__0.8001__ 0.85 __1.75% 2__0.8501__ 0.90 ___2.00% 3__0.9001___0.95___2.75% 4 5 Purchase Price 6 Down Payment 7 Mortgage Take note that I converted the percentage value of the ratio of the down payment and purchase price into decimals. Before I proceed, here are some clarifications: Cell A5 is where you input the purchase price. Cell A6 is where you input the down payment. Cell A7 is where you will find the mortgage Based on what I've understood, the mortgage is obtained using the equation: =if(A6/A5<=B1,C1, if(A6/A5>=A3,C3,C2)) If this doesn't work, try to contact me here."

Home Mortgage Excel Help? I am having trouble trying to figure out the formulas for a spreadsheet. The Information in the spread sheet includes: Price of home: $650,000; Down Payments: 5%; Interest Rate: 6%; Term of Loan: 360. The information that I need to find is: Down payment Amount, Loan Amount. The only one that I seemed to figure out was the Monthly Payment, which came out to $3,897.08. Thanks to anyone who can help me out.

Need formula on MS Excel to make amortization calculator? i'm making a chart of 36 payments (rows 1-36) so i need to make each column for payment (column A), interest (column B), principle (column C) and balance (column D). I just want to input the loan amount in one cell and get a result for the interest paid, principle paid and balance for all 36 payments. If you dont understand visit This is what i'm trying to make on excel

Helpplease. Business n Finance..(Excel users)? Consider the following scenario: John buys a house for $150,000 and takes out a five year adjustable rate mortgage with a beginning rate of 6%. He makes annual payments rather than monthly payments. Unfortunately for John, interest rates go up by 1% for each of the five years of his loan (Year 1 is 6%, Year 2 is 7%, Year 3 is 8%, Year 4 is 9%, Year 5 is 10%). Calculate the amount of John's payment over the life of his loan. Compare these findings if he would have taken out a fix rate loan for the same period at 7.5%. Which do you think is the better deal? ok that is my assignment.. Any one know what excel formulas i could use. My reading material is not detailed enough for my to perform this byhand.

DN replied: "There are template in your excel for calculating these... just need to punch in the numbers."

MATH! Mortgage/Conventional Loan help? I recently heard from a friend that making payments on my mortgage (and student loans) at the beginning of the month--instead of waiting until the end--will save me a lot of money in the long run. Im in a $100k, 30 year mortgage at 5.5%. So the only equation that I found online was: ________P(r/n) A=______--------- _____1-(1+r/n)^-(nt) Where: a= initial deposit? r= interest rate n= how many times in a year the interest rate goes into effect t= number of years The problem I'm having is that I'm pretty sure that this formula calculates for if I pay at the end of a month, I'd like one that calculates for the beginning of the month. I'm pretty rusty at math but I do like to be able to figure things out on my own. I'd really appreciate it if you could help me find the correct formula. A couple people suggested that I use Microsoft Excel to figure out the problem. Well, I am lost when trying to use Excel, so I'd really like the formula. Thanks all! Please do not answer unless you can help me with the equation. :)

sassy2 replied: "Sorry your friend is wrong. The interest is for the month and paying it earlier accomplishes nothing. Adding a 100 bucks to your monthly payment and marking it as extra principle is the only way to shorten a loan of any type."

excel question? if i buy a house that costs 279500,with a 30 year loan at 223600,with a down paymnent of 55900 or 20%,with 6.55% interest and have 180 payments(monthly),property taxes is 4,127.15,with homeowners insurance of 83.33 a month and mortgage insurance of 122 a month..no points,no fees,closing cost of 4%...how do i put all this into excel??a link with the awnser in excel would be nice,i need the formulas too..thanks

fcas80 replied: "cell A1: Loan cell A2: Term cell A3: Annual interest cell A4: Monthly interest cell A5: Annual taxes cell A9: Monthly loan payment cell A10: Monthly taxes cell A11:Monthly HO insurance cell A12:Monthly mortgage ins cell A13: Monthly outlay cell B1: 223,600.00 cell B2: 180 cell B3: 6.55% cell B4: =B13/12 cell B5: 4,127.15 cell B9: =-PMT(B4,B2,B1) cell B10: =B5/12 cell B11:83.33 cell B12:122 cell B13: =SUM(B9.B12)"

another question? if i buy a house that costs 279500,with a 30 year loan at 223600,with a down paymnent of 55900 or 20%,with 6.55% interest and have 180 payments(monthly),property taxes is 4,127.15,with homeowners insurance of 83.33 a month and mortgage insurance of 122 a month..no points,no fees,closing cost of 4%...how do i put all this into excel??a link with the awnser in excel would be nice,i need the formulas too..thanks

pattibcacl replied: "don,t know sorry"

daniel r replied: "don't know what exactly you are looking for in a spreadsheet, but why are you paying mortgage insurance with a 20% downpayment???"