Trending Content
| +82 | LP coming into deal at higher land basis. How to model returns to GP? | 18 | 11h | |
| +76 | Increasing RE Industry's Cash Compensation, Collectively | 43 | 9h | |
| +72 | Major Decisions | 13 | 1d | |
| +28 | Niche down to an operator or stay an allocator | 9 | 3d | |
| +24 | Breaking into CRE late 20s | 7 | 3d | |
| +23 | Thoughts on joining an early-stage REPE fund | 7 | 9h | |
| +22 | Self Storage ECRI | 9 | 2d | |
| +20 | Doom Loop or Creative Urban Reemergence? | 3 | 6d | |
| +19 | How to get good at RE Modelling? | 16 | 4m | |
| +18 | Henderson Park London | 21 | 11h |
Career Resources
Did you divide interest (I) and term(N) by 12?
I divided interest by 12 and I have columns with years and periods
Occasionally I mess up on my AM tables too. I find I usually forget to hard code (F4) properly.
LOL...been there. It's always the easy stuff dude.
Uploading the amort table here would be a good start..
gave you a SB, thanks again for the help.
A standard table as in a fixed rate loan schedule? I have no real estate experience, so please excuse me if my answer is not what you're looking for. With that being said, I'll add my two cents as to how I view amortization. If it helps you, great. If it doesn't help you, it will help someone else in the future.
Your payment in an amortization schedule shouldn't be decreasing by anything, your principal balance and interest part should be decreasing. Utilize Excel's "=PMT()" function to calculate the payment; this amount will stay constant.
For example, if this was a $100,000 5 year fixed rate loan at 5% with monthly payments it would come out to $1,887.12 (=PMT(5%/12,12*5,-100,000,0) Note, if each piece of this formula is referencing a cell, each cell reference should be fully locked ($A$1).
The interest part of this would simply be the principal balance multiplied by your interest rate divided by your payments per year. Maintaining the numbers from above, (=100,000*(5%/12)) you would have $416.67 as the interest part in period one. For this, the principal balance should not be locked, but if you are referencing cells for the IR and PPY they should be.
The principal part is easy, as you just subtract that period's interest part from the payment you first calculated =PMT(). This gives you $1,470.46 for your principal part in period one.
Finally, your principal balance will be last periods balance minus the current periods principal part. For period one, last periods balance is the original loan amount. This should be shown on a row noted as period 0. There will be no payments, interest part, or principal part shown in period 0. The calculation of principal balance should not be locked, as you need to reference the cells for this to work.
To reiterate, if done correctly, your payment will stay the same throughout the schedule, the interest part of your payment should decrease, and the principal part will increase. As this happens, your principal balance is reduced until 0 is shown in the final period.
For reference, your first 2 periods should calculate as shown below (hopefully no formatting errors occur upon posting). When you begin your schedule, I first suggest labeling each period in your first column. In this case it would go to 60 (it will always be your number of years multiplied by your payments per year). There are ways to make this dynamic with your inputs, but we'll keep this as simple as possible. If you built it out correctly, after dragging your cells down to the last period, you'll find a 0 in the last principal balance cell.
Hope this helps someone out there.
Period Payment Interest Part Principal Part Principal Balance 0 100,000.00 1 $1,887.12 $416.6 $1,470.46 98,529.54 2 $1,887.12 $410.54 $1,476.58 97,052.96
I appreciate the help.
I would call my modeling at worst advanced. Every now and then excel sort of freezes or doesn't do what it is supposed to do. I have to usually quite the program then restart excel and it works properly. Let's just say in this case I have done each piece of the amort table separately and it appears to calc out minus some small value that is not attributable to "programmer" error. being off by 200 a month on a 30 million loan leads me to suspect it is something else than my excel being screwed up.
I figured as much. Also, to be sure I'm not coming off in a way unintended, by no means was my lengthy explanation of an entry level finance topic meant to imply inexperience or a lack of modeling skill. I figured, in the likely case that it didn't help, it could be used by students searching the forum for finance help in the future. I've had my fair share of issues with Excel as well, and know how frustrating it can be. Hopefully the error resolves itself for you. Best of luck!
Feel free to send over your am schedule in excel format, and I'll help you out. My email is spencer at assessre dot com.
Ea nihil quia eos nostrum officia ut. Non officia fugit officia molestiae laborum et. Beatae enim aut architecto quae ab fugiat et.
Et dolores molestias vitae. Consequuntur et beatae sit libero voluptas aliquam distinctio quas.
Fugit nobis aliquam autem provident id. Autem assumenda provident aut occaecati consectetur.
Molestiae quo quo qui voluptates. Est praesentium in dolorem odit. Quisquam expedita qui sequi illum. Nostrum laudantium maxime laudantium est et et. Corrupti dolores doloremque odio saepe veniam. Nisi ut dicta doloribus nulla rem vitae.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...