Complicated formula(s)
I am attaching a spreadsheet that needs formula(s) in the yearly report tab. I would like to be able place the year, for ex: 2011, in cell A17. I would then like the report to populate information from the data tab into the yearly report tab so that the schedule shows the entire year's activity in the schedule, the end date and start date cells in B21 and B22 respectively show the dates of year jan1/dec31, and the info in row 17 generates all of the information as the title requests. As it is now, it only works for a specific period of time. I need an entire year. Any help would be greatly appreciated.
Copy-of-Amortization-on-a-Simple.xls
Copy-of-Amortization-on-a-Simple.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, do you want the values in cells B17, C17, and D17 to show a summary of payments, interest and principal for the year shown?
ASKER
What i would like would be able to do is not touch the report tab whatsoever. If i could have a formula populate the current year in A17 of the Yearly reports tab, and that would pull the info from the data tab to populate all other fields, is what I am looking for. I want it to show January 1-December 31 regardless if loan starts in the middle of the year. And, yes, I would like B, C, and D 17 to calculate the total payments, interest, and principal paid for the year shown.
Thanks,
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
On the good side, it can show the value for an entire year. On the bad side, if I put in 2011 in the year field it produces an NA response in the cells, likely due to the fact that the loan start date is in the middle of the year.
Is there any way cell A17 can recognize what year it is, and automatically populate that year date, and thereby fill in the dates for the schedule?
Also, Cells B17, and D17 are easy enough to calculate, and i have formulas for those, but the Total Interest Paid for the year is difficult. The formula that calculated the Interest paid for the monthly report is this: =INDEX(Data!$B$18:$K$11017
This calculates the amount of interest paid at each payment, but I would like a formula that calculates the total interest paid over the entire year. I'm not sure how to calculate that.
I really do appreciate your help
Is there any way cell A17 can recognize what year it is, and automatically populate that year date, and thereby fill in the dates for the schedule?
Also, Cells B17, and D17 are easy enough to calculate, and i have formulas for those, but the Total Interest Paid for the year is difficult. The formula that calculated the Interest paid for the monthly report is this: =INDEX(Data!$B$18:$K$11017
This calculates the amount of interest paid at each payment, but I would like a formula that calculates the total interest paid over the entire year. I'm not sure how to calculate that.
I really do appreciate your help
binaryman101,
Here's a VBA solution. Not sure if it's acceptable. Select a new date from Cell A17 and it will automatically populate the rows below
Kyle
Q-27315751-RevA.xlsm
Here's a VBA solution. Not sure if it's acceptable. Select a new date from Cell A17 and it will automatically populate the rows below
Kyle
Q-27315751-RevA.xlsm
Are you constrained to using Excel 2003? I have a solution for you that does not use any macros AND works for all years. But has to use 2007/2010.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
everyone's help made my spreadsheet possible, thank you very much!
ASKER
Thanks,