Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily loan calculation
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Daily Sales to Date calculation | Excel Discussion (Misc queries) | |||
How to calculate loan instalment daily rest | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
table | Excel Worksheet Functions |