Solved

# Complicated formula(s)

Posted on 2011-09-19
170 Views
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
Question by:binaryman101

LVL 32

Accepted Solution

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

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

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

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

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

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

Author Comment

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

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

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

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

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

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

LVL 27

Assisted Solution

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

ID: 36569534
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!