Solved

Complicated formula(s)

Posted on 2011-09-19
13
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 33

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 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