#1
|
|||
|
|||
start row of mortgage info based on input date
the years are in Row 1. 2020, 2021, etc. The user is planning to purchase a house, but doesn't know what year. The data rows include fair market value, outstanding mortgage, equity, interest paid during the year and the tax value of the interest deduction. The mortgage is a function of what year and also the purchase price which grows over time, so if he buys in 2025, he'll pay more and have a larger mortgage than if he purchases in 2023. I know the purchase price, outstanding mortgage, and mortgage paymens over the various possible years. My current practice is to have the amortization schedule on another worksheet and simply refer to it (lookup the outstanding balance every year and the sum of the interest payments). I then clumsily insert/delete cells to move the block of data left and right to get the purchase year correct. But that generates errors in cells below that reference the data block. I can redraw those cells with errors, but it is awkward. I thought of using a lookup function, but cell C5 (for example) sometimes refers to data from year 3 and sometimes to year 4, etc., depending on when the buyer plans to purchase the house. Is any of this making sense? |
#2
|
|||
|
|||
i dont really under stand the calc but i think it should be possible to make it dynamic.
Can you attach an example workbook for me to play with? |
#3
|
|||
|
|||
cleaned up example attached
I have been moving rows 22-34 left and right by inserting/deleting cells, but you'll see that throws errors in rows 48 and 55. I can solve that by redrawing rows 36-57 after I move the rows dealing with the apartment purchase. Cells B16-B20 are hard input numbers. The rest of rows 18-20 are just references for me to show what the value and expenses will be as the years pass for the target apartment. Those numbers are not involved in the calculations. As you will see, rows 26, 30 refer to the amortization schedule on a different worksheet. I can't calculate these numbers easily. The mortgage reduction is not linear as one pays less interest as the years go by. As a side note, the equity number in line 27 is not a true sum. I included the possibility that I would relocate to an area where housing costs are cheaper. The adjustment is in the equity number even though the FMV and mortgage would be lower in that scenario. Have fun.
|
#4
|
|||
|
|||
A lot of numbers!
To try and break it down into manageble chunks
I have highlighted what i think are the varibles that you would type in, in blue. and pretty much everything else will calculate from that? for example i would think that b1 should always be the current year or maybe it should equal the move out date? Ideally if we can get the years to be dynamic the calculation will work from them and we can hide what we dont want. In my mind nothing should move as each calc can look for the correct detail It might be a slow approch but we can get a decent spready at the end of this |
#5
|
|||
|
|||
progress
Thanks for the effort. My thoughts:
|
#6
|
|||
|
|||
Okay will forget about 1 t 6 for the time being
Based on t he attached example what column do you think rows 22 to 34 should start? |
#7
|
|||
|
|||
starting
cell B16 says the year of apt. purchase. rows 22-34 should align in that year, in this case 2027, or column J. I can't remember if B16 is connected programatically anywhere. I don't think it it.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Get all rows based on input values from a list | soolsen | Excel | 1 | 03-27-2016 08:11 PM |
How do I Edit New Contact input info in Outlook 2013 | Grandpa_Nole | Outlook | 0 | 05-07-2015 11:28 AM |
Input data in Excel and get info from Access | zkibz | Excel Programming | 1 | 08-14-2014 05:17 PM |
Mortgage repayment calculations | BritBiker2 | Excel | 2 | 10-14-2013 10:55 PM |
Look up an array based on user input | johnsmb | Excel | 2 | 01-07-2011 01:12 PM |