Solved

Complicated formula(s)

Posted on 2011-09-19
13
160 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:binaryman101
13 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 100 total points
ID: 36561605
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
 

Author Comment

by:binaryman101
ID: 36561698
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 300 total points
ID: 36561787
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36561793
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
 

Author Comment

by:binaryman101
ID: 36561858
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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 100 total points
ID: 36562121
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:binaryman101
ID: 36562465
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36562481
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36562771
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 300 total points
ID: 36562846
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36562947
Here you go...you can still use this with Excel 2003.

Amortization-on-a-Simple--edit.xls
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 300 total points
ID: 36562974
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
 

Author Closing Comment

by:binaryman101
ID: 36569534
everyone's help made my spreadsheet possible, thank you very much!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now