Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Daily loan calculation

I have written a spreadsheet which calculates the date on which a
loan/mortgage will finally be paid off. Can anyone tell me how to set it up
so that the regular (monthly) payment falls on the same day each month,
taking into account 28/29/30/31 day months?

At the moment I have a Loan Start Date entered in cell H14 and a First
Payment Date in H16. Cell B22 = H16, and column B then automatically fills
down to provide approximately 30 years of individual dates, including leap
years. What I now need to do is have some kind of formatting for the Payment
column (in my case, D), which says =IF(H16=the eighth of the month, or
whatever, then make this payment every month on the eighth). HELP!!!! I've
been trying for a week now to do this!

(None of the templates I have been able to find allow you to amortize a loan
on a daily basis - the best I have seen is on a bi-weekly basis, which
doesn't work for the kind of loan I have)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Daily loan calculation

Hi

With your start date in say cell A1
=DATA(YEAR(A1),MONTH(A1)+1,8)
Copy down as far as required

--
Regards

Roger Govier


"Dr. Zhivago" wrote in message
...
I have written a spreadsheet which calculates the date on which a
loan/mortgage will finally be paid off. Can anyone tell me how to set
it up
so that the regular (monthly) payment falls on the same day each
month,
taking into account 28/29/30/31 day months?

At the moment I have a Loan Start Date entered in cell H14 and a First
Payment Date in H16. Cell B22 = H16, and column B then automatically
fills
down to provide approximately 30 years of individual dates, including
leap
years. What I now need to do is have some kind of formatting for the
Payment
column (in my case, D), which says =IF(H16=the eighth of the month, or
whatever, then make this payment every month on the eighth). HELP!!!!
I've
been trying for a week now to do this!

(None of the templates I have been able to find allow you to amortize
a loan
on a daily basis - the best I have seen is on a bi-weekly basis, which
doesn't work for the kind of loan I have)



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Daily loan calculation

Roger, thanks. I have tried what you suggested, but it just returns NAME?



"Roger Govier" wrote:

Hi

With your start date in say cell A1
=DATA(YEAR(A1),MONTH(A1)+1,8)
Copy down as far as required

--
Regards

Roger Govier


"Dr. Zhivago" wrote in message
...
I have written a spreadsheet which calculates the date on which a
loan/mortgage will finally be paid off. Can anyone tell me how to set
it up
so that the regular (monthly) payment falls on the same day each
month,
taking into account 28/29/30/31 day months?

At the moment I have a Loan Start Date entered in cell H14 and a First
Payment Date in H16. Cell B22 = H16, and column B then automatically
fills
down to provide approximately 30 years of individual dates, including
leap
years. What I now need to do is have some kind of formatting for the
Payment
column (in my case, D), which says =IF(H16=the eighth of the month, or
whatever, then make this payment every month on the eighth). HELP!!!!
I've
been trying for a week now to do this!

(None of the templates I have been able to find allow you to amortize
a loan
on a daily basis - the best I have seen is on a bi-weekly basis, which
doesn't work for the kind of loan I have)




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Daily loan calculation

Thanks Don
Now if only I could control those fingers on the keyboard!!!!

--
Regards

Roger Govier


"Don Guillett" wrote in message
...
dat E

--
Don Guillett
SalesAid Software

"Dr. Zhivago" wrote in message
...
Roger, thanks. I have tried what you suggested, but it just returns
NAME?



"Roger Govier" wrote:

Hi

With your start date in say cell A1
=DATA(YEAR(A1),MONTH(A1)+1,8)
Copy down as far as required

--
Regards

Roger Govier


"Dr. Zhivago" wrote in message
...
I have written a spreadsheet which calculates the date on which a
loan/mortgage will finally be paid off. Can anyone tell me how to
set
it up
so that the regular (monthly) payment falls on the same day each
month,
taking into account 28/29/30/31 day months?

At the moment I have a Loan Start Date entered in cell H14 and a
First
Payment Date in H16. Cell B22 = H16, and column B then
automatically
fills
down to provide approximately 30 years of individual dates,
including
leap
years. What I now need to do is have some kind of formatting for
the
Payment
column (in my case, D), which says =IF(H16=the eighth of the
month, or
whatever, then make this payment every month on the eighth).
HELP!!!!
I've
been trying for a week now to do this!

(None of the templates I have been able to find allow you to
amortize
a loan
on a daily basis - the best I have seen is on a bi-weekly basis,
which
doesn't work for the kind of loan I have)









  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Daily loan calculation

I do it yall tha tyme

--
Don Guillett
SalesAid Software

"Roger Govier" wrote in message
...
Thanks Don
Now if only I could control those fingers on the keyboard!!!!

--
Regards

Roger Govier


"Don Guillett" wrote in message
...
dat E

--
Don Guillett
SalesAid Software

"Dr. Zhivago" wrote in message
...
Roger, thanks. I have tried what you suggested, but it just returns
NAME?



"Roger Govier" wrote:

Hi

With your start date in say cell A1
=DATA(YEAR(A1),MONTH(A1)+1,8)
Copy down as far as required

--
Regards

Roger Govier


"Dr. Zhivago" wrote in message
...
I have written a spreadsheet which calculates the date on which a
loan/mortgage will finally be paid off. Can anyone tell me how to
set
it up
so that the regular (monthly) payment falls on the same day each
month,
taking into account 28/29/30/31 day months?

At the moment I have a Loan Start Date entered in cell H14 and a
First
Payment Date in H16. Cell B22 = H16, and column B then automatically
fills
down to provide approximately 30 years of individual dates, including
leap
years. What I now need to do is have some kind of formatting for the
Payment
column (in my case, D), which says =IF(H16=the eighth of the month,
or
whatever, then make this payment every month on the eighth). HELP!!!!
I've
been trying for a week now to do this!

(None of the templates I have been able to find allow you to amortize
a loan
on a daily basis - the best I have seen is on a bi-weekly basis,
which
doesn't work for the kind of loan I have)









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Daily Sales to Date calculation nander Excel Discussion (Misc queries) 7 June 10th 06 07:41 PM
How to calculate loan instalment daily rest kudos Excel Worksheet Functions 3 October 7th 05 04:11 AM
Calculation Setting in Excel Stuart Bisset Excel Discussion (Misc queries) 0 June 17th 05 09:54 AM
table dow Excel Worksheet Functions 0 January 11th 05 06:17 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"