Mortgage interest rates

Hi,
I'm building a spreadsheet to monitor the status of my mortgag
repayments.
Each monthly payment is part interest and part repayment so that eac
month the outstanding amount reduces and therefore the interest an
repayment parts change each month.
The calculation refers to a fixed cell called interest_rate. If th
interest rate changes, how do I stop the cells showing the payment
already made recalculating at the new interest rate?
Thanks 
Sand

--
sreidp
-----------------------------------------------------------------------
sreidpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3534
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

0
6/12/2006 8:27:43 PM
excel 39880 articles. 2 followers. Follow

2 Replies
334 Views

Similar Articles

[PageSpeed] 36

put it in the formula or in a separate column so you will know that it is 
reflecting the change.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"sreidpe" <sreidpe.29b3bz_1150144201.4871@excelforum-nospam.com> wrote in 
message news:sreidpe.29b3bz_1150144201.4871@excelforum-nospam.com...
>
> Hi,
> I'm building a spreadsheet to monitor the status of my mortgage
> repayments.
> Each monthly payment is part interest and part repayment so that each
> month the outstanding amount reduces and therefore the interest and
> repayment parts change each month.
> The calculation refers to a fixed cell called interest_rate. If the
> interest rate changes, how do I stop the cells showing the payments
> already made recalculating at the new interest rate?
> Thanks
> Sandy
>
>
> -- 
> sreidpe
> ------------------------------------------------------------------------
> sreidpe's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=35347
> View this thread: http://www.excelforum.com/showthread.php?threadid=551202
> 


0
dguillett1 (2487)
6/12/2006 8:33:49 PM
sreidpe wrote:
> I'm building a spreadsheet to monitor the status of my mortgage
> repayments.  [....] The calculation refers to a fixed cell called
> interest_rate. If the interest rate changes, how do I stop the cells
> showing the payments already made recalculating at the new
> interest rate?

You cannot, not with a single "fixed cell called interest_rate".  I
would do one of two things:

1. Have a table of applicable interest rates, and refer to the
appropriate table entry in each formula that relies on the applicable
interest rate.

2. Have a column for interest rate, just as I presume you have a column
for payment amount, and the formulas that rely on interest rate refer
to the cell in their respective row.  In other words, you replicate the
applicable interest rate down the column.

There are fancy ways of accomplishing #1 so that you do not have to
"hardcode" a cell reference; for example, lookups based on date.  But I
think that is "killing an ant with a sledgehammer".  KISS.

0
joeu2004 (766)
6/12/2006 9:25:44 PM
Reply:

Similar Artilces:

obtaining exchange rates
Hello all, Is there some way of obtaining the exchange rate for two currencies an updating a column automatically? (based on an internet site or anothe way) I have seen something similar for stock quotations and was wondering i there is a way for exchange rates. Thank -- Message posted from http://www.ExcelForum.com Currency Converter Powering Foreign Exchange Rate Calculations with Live Currency Rates http://www.2globalmart.com/currency-converter.html Links to similar things can be found on my http://www.mvps.org/dmcritchie/excel/stocks.htm HTH, David McRitchie, Microsoft MVP - ...

Calculating an hourly rate
Hi all, I have a quick question. I have a field in hours format and a field in currency format. I want to calculate the hourly rate for work done. For example �20 was earned in 2 hours. Easy for us to work this out but is there any way Excel can calculate this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ By field in hours, I assume you mean time such as hh:mm format? Assume hours in A1, amount in A2, rate is then =A2/A1/24 The division by 24 is needed as t...

How to change monthly mortgage payment to biweekly?
Using Money 2002 Deluxe: I'm set up on a monthly basis at present but will shortly be making b-weekly mortgage house payments. How do you change money without lsoing all the history? Thanks! Just change the scheduled payment frequency. Don't change the loan. (Here I'm assuming that you are just making extra payments and have not been sucked into one of the deals where they "manage" your biweekly payments for a nice fee. If the latter is the case, then you might consider closing one loan and opening another.) "Pauldcwa" <pauldcwa@comcast.net> wrote in...

template for monthly interest expense for invested monies
I need a template or formula suggestions that easily creates the monthly interest expense for shareholder loans. principal x (annual interest rate/12) = monthly interest use a cell reference for the principal and interest rate and you can copy the formula. Hope that helps. -Wendy K. "kbergsma" wrote: > I need a template or formula suggestions that easily creates the monthly > interest expense for shareholder loans. ...

FIND the BEST MORTGAGE RATES,mortgage calculators,Second mortgage
http://www.webfreeguide.com/Mortgage-Brokers.aspx Mortgage Brokers Directory has a large list of mortgage companies in the United sates,UK,Canada and Australia.you can find the best mortgage rates,mortgage calculators,mortgage refinancing sources,second mortgage,mortgage quotes,bad credit mortgages and lot's of other online mortgage informatoin here. http://www.webfreeguide.com/Mortgage-Brokers.aspx ...

Mortgages and Credit Cards
I need help/advise with my mortgage account and associated checking account. Here's what I have: My mortgage payment comes directly out of one of my checking accounts every month. I go online, the information is downloaded, everything is cool. I set up a mortgage tracking account in Money, entering all the information correctly, including that the payments come from the afore mentioned checking account. This is supposed to track how much I owe on the principle as well as let me know how much of my payment is actually being applied to the principle. What's happening is th...

Budget and Mortgage
My Mortgage payment does show up under the Debt section in my budget. My Mortgage payment category is Loan Payment: House Loan in my checking account from which I pay my mortgage. So far so good. However when I run the budget - spending report. The Budget column does not show the budget amount for the Mortgage. The Actual column does under the Debt Category does. It thus looks like I spent more, when this item is actually budgeted. This does not make sense. MSMONEY documentation is POOR. Why cant we have a complete comprehensive ..pdf style documentation as is expected with any comme...

Dividend and Interest Reinvestment
I have stocks that I manually enter the dividends for each month. I also have bonds that I manually enter the interest payments for each month. This is working great, and shows me the value of each investment. However, every now and then I take the income from dividends and interest and purchase more stocks or bonds. How do I enter the new purchase of stocks or bonds so that it reflects the fact that I am reinvesting interest and dividends earned? If I just enter, for example, a new stock buy, it overinflates the value of my portfolio. Cheers! I use the Reinvest Dividend and Rein...

Just Out of Interest
I was wondering, when Microsoft sit down to write the next version of Excel, say for 2006, does anyone know how they decide what to add or amend or remove? S:) Yes is probably an unsatisfying reply, but Yes, of course. Those people are not allowed to answer your follow-up question, so don't ask it. Best wishes Harald "Steve" <crankylemming@aol.com> skrev i melding news:1103146538.084504.53960@z14g2000cwz.googlegroups.com... > I was wondering, when Microsoft sit down to write the next version of > Excel, say for 2006, does anyone know how they decide what to add o...

MS Money Mortgage Account
I currently running MSM03. I have a mortgage account set up for the monthly payments. I set the account up based upon the amount of loan, monthly payment, rate and escrow details. When I pay the mortgage from my checking account, it automatically calculates the principal and interest per the orignal set up. Recently the calculated values are incorrect. For instance this month, an 1,800.00 payment should have calculated the payment to be ~801 (P) and ~789 (I), plus fixed excrow. It instead calculates tne principal as ~1,500.00 splits the fixed escrow and then calculates the interest as wh...

Mortgage Cycling Pays Off Mortgage Early With Mortgage Reduction
Mortgage cycling allows you to build at least $40,000 worth of home equity in a very short period of time without making any changes to your current mortgage. Pay your mortgage off faster than using a biweekly mortgage plan. With Mortgage Cycling you don't need to be an expert with mortgages or a math wizard either. Mortgage Cycling will benefit you whether you're a first time homeowner or a veteran real estate investor. http://mortgageyd.blogspot.com/# ...

Setting up a mortgage
I set up my house mortgage in Money 2005, all went well, Money went back and recreated all my past payments but today my mortgage payment came due but Money did not automatically update the mortgage account to show the payment. Was there a question here? Did you schedule a loan payment? Did you enter the scheduled payment in the register? "Patrick Simonds" <ordnance1@comcast.net> wrote in message news:uJjzrN1qEHA.1160@tk2msftngp13.phx.gbl... >I set up my house mortgage in Money 2005, all went well, Money went back >and recreated all my past payments but today my...

FRX Multi Currency
Is there any work around when FRX DAX is not working as it is used to work with FRX Currency Translation Reporting? When using more than one data/record in DAX Table in one month/year - Historical eg. Retained Earnings Account. FRX is always returning an error message that the Balance in DAX Table is not equal to Great Plains computed Balance. This is causing discontentment from the client since the software can not deliver what it is intended to do. Instead of effeciency in Reporting, you have to manually compute the translated balances of Financial information. You can define onl...

Mortgage Deductions
I want to set up a Mortgage account so that when I enter the deduction in my Checking account it will decrease the remaining balance of my mortgage. When I followed the wizard to do this, and entered my deduction in my checking account, it would Increase the balance on my home loan. What am I doing wrong? In microsoft.public.money, <randykwalker@hotmail.com> wrote: >I want to set up a Mortgage account so that when I enter >the deduction in my Checking account it will decrease the >remaining balance of my mortgage. >When I followed the wizard to do this, and entered m...

Pivot table compound interest
Hello I am trying to make a pivot table with a custom "Grand total" calculation. For example I need to calculated compound interest in the grand total field. Is this possible? Suppose 10% return every month (Jan-Dec)compounded monthly. jan feb mar ... TOTAL 2001 10% 10% 10%... 313.8% 2002 10% 10% 10%... 313.8% 2003 10% 10% 10%... 313.8& ------------------------------- TOTAL = exp(sum(log(1+n)) ANY HELP IS APPRECAITED!!!! ...

Mortgages
How do I handle mortgages that I own? My son wanted to refinance his house mortgage and I agreed to supply the funds. He now sends me a monthly check. Can someone tell me how to set this up in money2003 and keep the information current? You can setup a new loan account; one of the first questions it asks is if you are borrowing or lending. Say lending. Once you've got the loan setup, you can create loan payment transactions every time you get one. These transactions will account for the interest income and reduce the principal balance. "Fran k" <skans_98@yahoo.com>...

Debt interest... in the budget planner.
I have set up a debt plan and a budget. The debt plan is simple, since I have only a mortgage. For simplicity, let's say my required mortgage payment is $1000 a month. I add the mortgage account and choose to base my plan on 'what I want to pay each month'. Since I want to prepay my mortgage, I then tell the Debt Planner that I have $2000 for my 'monthly allocation for debt' ( $1000 required + $1000 pre-payment). It appears that everything sets up fine. My payment appears in the monthly Bills and Deposits schedule. The split on the payments shows the extra $1000 as a...

mortgage
How do you show an extra payment every month How do you link the transaction (auto withdraw) to the account set up for a loan Are there any courses on Money Answer to question--I'm guessing, you seem allergic to punctuation--number 1: If separate, create a recurring transfer to the loan account. The loan account name may not show in the Transfer:[acct] pulldown. Type it in. If combined with your regular payment, just increase the amount of the regular loan payment and, in the splits, assign the additional amount to a Principal Transfer. Answer to question number 2: See above. I'm...

mortgage payment
I've been trying to do a spreadsheet with a Princple and Interest Payment. I've tried the Pmt= formula builder and the payment come out about $200 higher than it should base on by tried and true HP12C. Is there an alternate formula that i can use? G On Mon, 13 Sep 2004 15:45:15 -0700, "Gordon" <gordonmkane@hotmail.com> wrote: >I've been trying to do a spreadsheet with a Princple and Interest Payment. >I've tried the Pmt= formula builder and the payment come out about $200 >higher than it should base on by tried and true HP12C. Is there an alternate ...

Mortgage offset accounts?
Do any versions of Money allow you to create a mortgage offset account? What is a mortgage offset account? <anonymous@discussions.microsoft.com> wrote in message news:024f01c3b6d0$d3e922b0$a301280a@phx.gbl... > Do any versions of Money allow you to create a mortgage > offset account? It's something we have on this side of the pond, where although you might have a savings account, current/checking account and loan, all the money is pooled and you pay interest on the total, rather than on just the loan balance. -- Glyn Simpson, Microsoft MVP - Money Check http://suppor...

Entering Savings, or Cash Account Interest
How is interest earned by a money market or brokerage cash account entered, so the program can calculate interest rate and amount earned? Up to now, I use the 'Deposit" tab, and enter the amount and call it "Interest" but it seems to me that there should be a drop down menu that lists "interest or dividend" like in the investment accounts so the program can calculate earnings. The way I do it, it just seem like another deposit, which it is not. -- Dave Schwartz Commack, NY Well, it's not just another deposit, quite. You should categorize the income as s...

mortgage
Hi. I am new to excel. I have never used it before in my life. I need help creating an amortorization schedule. I guess that would consist of setting up the cells so that one can enter the PV, YEARS, AND INTEREST RATE. and the results shoudl show the payments seperated as principle and interest for the number of years entered,,,and at the end of the schedule it should show the total number of interst paid . can you please help??? Take a look here: http://office.microsoft.com/en-us/templates/ There are a number of premade templates for this. In article <04392B5A-7E7E-438D-AA31...

Moving the Area of Interest across the Video Sequence #2
Hello, I am capturing a live acquisition of image 1280*1024 and defined a subwindow or Area of Interest(AOI) of size 1280*40 using MFC programming .I want to move this AOI across the 1280*1024 window (across the video sequence). I am thinking of using a timer or a for loop so that it will iterate on each of the frames of the video. Can anyone please kindly help me regarding this. Thanks in advance James. ...

Re-mortgage
I have been using a loan account to track my mortgage payments. Recently I have re-mortgaged with another provider for a higher amount and I've set another loan account up. I want to be transfer the money into the old account to close it off and transfer the balance into the current account. Money doesn't seem to want to transfer money between loans. So how do I show that the loan is closed and principle paid off? I tried creating a catagory but then money reports a massive outgoing aganist it distorting all the reporting. Very sadly, Money makes it difficult to just transfer M...

how to move mortgage from checking account into mortgage, escrow acct
M05 Std how to move mortgage from checking account into mortgage, escrow acct? in order to categorize all of my checking account transactions, i am forced to learn about mortgages so i created a mortgage account along with the suggested asset account (the value of the house) going back to checking account, i took what used to be the incorrectly designated deposit into a payee named in the mortage banks honor, and changed it to a transfer account. the transfer "to" listing did not list my mortgage account, it only listed the asset account. this didn't makes sense to me and now...