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
binaryman101Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
How about setting up a pivot table on your data? The Year could be added as column in the data, calculated from the date column. Then the Year column could be used as a page field in the pivot.

Thanks
Rob H
0
 
binaryman101Author Commented:
I've only used pivot tables once or twice before....I think that would work, I just don't know how to make that happen.  Any suggestions?

Thanks,

0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Hi,

What do you want it to show for the starting and ending years?  For example, this sample loan starts on October 1 and has a loan day number of 1.  Do you want the Yearly Report tab to start with January 1, 2011 and still show Day 1?  Or do you want the Yearly Report to start with October 1, 2011 with Day 1 and then only show data through December 31, 2011?

-Glenn
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Glenn RayExcel VBA DeveloperCommented:
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?
0
 
binaryman101Author Commented:
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,
0
 
barry houdiniConnect With a Mentor Commented:
Hello binaryman101,

I think the way you have it now can show a years worth of data anyway, can't it? If yopu put 1/1/2012 in B21 and 31/12/2012 in B22 you'll get data for the whole of 2012.

To make it work off A17 only just format that cell as general and enter the required year. Change B21 to this formula

=DATE(A17,1,1)

and change B22 to

=DATE(A17,12,31)

I also put in formulas for B17,C17 and D17.....I'm not sure if they give the results you want....see attached

regards, barry
27315751.xls
0
 
binaryman101Author Commented:
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,MATCH(A17,Data!$B$18:$B$11017,0),6)+(INDEX(Data!$B$18:$K$11017,MATCH(A17,Data!$B$18:$B$11017,0)-1,7))

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
0
 
kgerbChief EngineerCommented:
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
0
 
Glenn RayExcel VBA DeveloperCommented:
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.
0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
By the way, here would be the formulas for total payments & total interest if using Excel 2007:

Total Payments:
=SUMIFS(Data!$F$18:$F$11017,Data!$B$18:$B$11017,">="&DATE(A17,1,1),Data!$B$18:$B$11017,"<="&DATE(A17,12,31))

Total Interest:
=SUMIFS(Data!$G$18:$G$11017,Data!$B$18:$B$11017,">="&DATE(A17,1,1),Data!$B$18:$B$11017,"<="&DATE(A17,12,31))

Total Principal would be nearly the same, just with a tweak in case no payments had been made:
=IF(B17-C17>0,B17-C17,0)

To get the current calendar year automatically, A17 would be:
=YEAR(NOW()))

However, I don't recommend that.  Pretend it's January 4, 2012 and you want to look at 2011 data... :-)

-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
Here you go...you can still use this with Excel 2003.

Amortization-on-a-Simple--edit.xls
0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Dangme...submitted file before commenting on changes:

1) Left cell A17 as a manual input.  That allows you to choose any year (great for retroactive searching)
2) Significant changes to formulas in amortization table below.  Any row having a date beyond December 31 of the year specified in cell 17 returns a zero.
3) Applied conditional formatting to the amortization table to hide values beyond the calendar year (i.e., clear borders and change font to white).  This resolves the issue with a partial year.

0
 
binaryman101Author Commented:
everyone's help made my spreadsheet possible, thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.