I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).

For example, in worksheet 1:

A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.

I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .

I'm open to a simpler solution if I not going about this correctly.

Regards and Thanks in advance.

JWH

It should be a simple lookup. Although most people would create a columnar
table, it appears you are doing it in rows. So you would use Hlookup. Something
like:

=hlookup(today(),sheet1!4:5,2,true)

--
Regards,
Fred


"JWH" wrote in message
...
I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).

For example, in worksheet 1:

A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.

I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .

I'm open to a simpler solution if I not going about this correctly.

Regards and Thanks in advance.

JWH

One more thing. You can also calculate the current balance of a mortgage with a
simple future value statement, as in:

=fv(monthly interest rate, #months, - payments, starting balance)

--
Regards,
Fred


"JWH" wrote in message
...
I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).

For example, in worksheet 1:

A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.

I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .

I'm open to a simpler solution if I not going about this correctly.

Regards and Thanks in advance.

JWH

Thanks! It is a columnar table so I used vlookup and it seems to work. I'm
not sure how the FV function works, but I'll play around with it.

Thanks again for the guidance.

"Fred Smith" wrote:

One more thing. You can also calculate the current balance of a mortgage with a
simple future value statement, as in:

=fv(monthly interest rate, #months, - payments, starting balance)

--
Regards,
Fred


"JWH" wrote in message
...
I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).

For example, in worksheet 1:

A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.

I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .

I'm open to a simpler solution if I not going about this correctly.

Regards and Thanks in advance.

JWH

Hi, Could you please tell me what,s " Starting balance" on this statement.

let said -monthly interest rate = 0.4291667 (5.15% annuly)
- # month= 12 (after 12 payments)
-payment = -593.36 (monthly payment)

and than ???

how much did I still own the bank after 12 payments? if i have a mortgage
for 100,000$


Regards,

JP

"JWH" wrote:

Thanks! It is a columnar table so I used vlookup and it seems to work. I'm
not sure how the FV function works, but I'll play around with it.

Thanks again for the guidance.

"Fred Smith" wrote:

One more thing. You can also calculate the current balance of a mortgage with a
simple future value statement, as in:

=fv(monthly interest rate, #months, - payments, starting balance)

--
Regards,
Fred


"JWH" wrote in message
...
I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).

For example, in worksheet 1:

A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.

I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .

I'm open to a simpler solution if I not going about this correctly.

Regards and Thanks in advance.

JWH