# loan

1. ### Roll rate model - Delinquent Interest for loans

Hello, I've a question regarding the calculation of Delinquent Interests for loans using roll rate model, was wondering if someone have already use this ? Thank you
2. ### Calculating daily interest

Hello, I have someone to whom I've been lending money at random times for the last 3 years, sometimes is \$600, sometimes can be \$2000. It's a friend so I've never paid much attention to how much this money would have given me if I had it in the bank or invested. But now the debt is considerable...
3. ### Loan amortisation

When we give a loan to a friend, the payments are at most erratic, some months they repay the exact emi, some months they skip all together and some months the pay excess. now most of the loan amortisation tables show the month of payment, emi amount, principal deducted, interest deducted and...
4. ### Nested IF (I think) help needed

I have a spreadsheet with a list of loans. Sometimes a loan has a late payment. I want to calculate the number of months that a loan interest payment is late if it's not yet reached maturity, or the number of months interest was unpaid if the maturity date has already passed. And, if there's...
5. ### Loan Amortization

Hi Examples of Loan Amortization schedules using Excel's PMT, IPMT functions are available, with a fixed interest and fixed tenure values. I have a loan which has a variable interest depending on the central banks' reference value. Due to the variable interest, the monthly payment is kept...
6. ### Loan Repayment Formula with Interest Capitalization and Monthly Payment

Hi All, I have a loan repayment schedule as below where interest accrued on loan principal is capitalized on quarterly basis (blue marked dates) and payment is made monthly. Here green cells are variable, meaning user should be able to define loan disbursement date (here, 7 OCT 2019), first...
7. ### Dynamic Loan Draw Formula

Hi All, I am wondering if I can enlist this forum's help. Can someone help me create a dynamic loan draw formula (highlighted row 10 in HTML table below) with the following constraints: - The loan cannot be overdrawn (i.e. cannot draw more from the loan than what is available in any given...
8. ### Loan Draw - Dyanamic Formula

Hi All, I am wondering if I can enlist this forum's help. Can someone help me create a dynamic loan draw formula (highlighted row 10 in HTML table below) with the following constraints: - The loan cannot be overdrawn (i.e. cannot draw more from the loan than what is available). The ending...
9. ### Weighted Average Loan Payment Formula

Hi everyone, I'm attempting to create a formula that calculates a "paid-to date" for a loan that has multiple principal balances. I want to be able to plug in a payment amount and have a formula in the Interest Paid-to column determine the paid-to date by applying the payment to the loan with...
10. ### Loan repayment calculation

Hi I want to take out a loan for 25,000,000 at 7% annual fixed interest rate and make month end monthly repayments. I want to take out the loan immediately "month 0" but not start paying it back until month 7... because I won't have any income for 6 months. I must pay the whole lot back by...
11. ### VBA Message Box Help...

I am working on a code to fire a message box is a certain CheckBox133 is selected and if cell B225 is greater than \$75,000. The code below works and message fires, the problem is that it fires any time a cell is changed. How do I limit this VBA to only changes to that checkbox and cell...
12. ### If Then Excel Formula

I need help with a formula Tab 1 Cell a1 with a manually entered date Tab 2 Column A Column B Loan # Dollar Amount Tab 3 Column A Column B Loan # Date If the date in tab 3 matches the date on tab 1 and Loan Number both tab 2 & 3 match Show me the loan # and the Dollar...
13. ### List of accounts if match date

I have two tabs. The first tab has a list of numbers numbers with dates. The second tab has the same loan numbers but has amounts with the account number. I know a VLOOKUP will work here but my boss is wanting a formula. I know a pivot table will work. So if I had a cell with a date and can...
14. ### Macro to find name in header and put in word in column below header matching the number of rows in another column

I'm trying to get the macro to look for "Mary Lee" in header (row 1) and puts in the word "LOAN" below it. It should go all the way down to match the number of rows in column A. The name "Mary Lee" may appear multiple times in the header. Data will change daily, so the number of rows in...
15. ### Excel Formula for Calculating Overdue Charges

Dear All, Please find the Loan EMI and Collection table below: <colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col></colgroup><tbody> As per EMI Schedule As per Collection Report Account Number Month Number Sum of EMI Cumulative EMI Due Date Sum of...
16. ### If-Statements, Multiple Stipulations to Create one Output

I have a reference chart that has many columns with many rows of information i have created a formula to read the chart and output an answer based on what it reads, but after further review I need the output to have one more stipulation...
17. ### Balance Sheet - Bring In Numbers From Other Tab/Sheet

I have to balance loan accounts daily and what would be the best way to do this? I have worksheet #1 which has a credit and debit side. I have a report I place on a second tab / Worksheet and add amounts posted to each loan and look for differences in amounts. Worksheet 2 I need to...
18. ### Index/Match formula for multiple values and columns with uneven length

Hi, This appeared to be a simple vlookup and match task but I kind of ran into a roadblock. I'm working on a financial report that shows amounts of aid students will receive for the year and semester. In sheet 1, I have a list of students that are coded with a unique ID number and an item type...
19. ### Optimising portfolio with constraints

Hi guys, Hoping you can help me with this problem. I'm looking at some loan data. There are some concentration limits (3 examples below) in place for the loan portfolio: 1: Sum of outstanding loans larger than 400k, cannot be greater than 5% of the total portfolio 2: Sum of outstanding loans...
20. ### loan calculations

How do i post an xls file? thanks

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back