Разработка программы для автоматического расчета кредитных и депозитных операций при помощи Visual Basic for Applications

• Введение
• 1. Структура коммерческого банка
• 2. Основные операции коммерческого банка
• 2.1 Пассивные операции
• 2.2 Активные операции
• 2.3 Комиссионные операции
• 3. Язык программирования Visual Basic for Applications
• 4. Постановка задачи
• 5. Математическая формулировка задачи
• 5.1 Расчёт ежемесячных выплат при аннуитетных платежах
• 5.2 Расчёт ежемесячных выплат при дифференцированных платежах
• 6. Описание работы программы
• 7. Разработка модуля программы расчёта кредитов и депозитов
• 7.1 Схема алгоритма выполнения основной программы
• 7.2 Используемые подпрограммы
• 7.2.1 Подпрограмма сохранения [4]
• 7.2.2 Подпрограмма расчета кредита [2]
• 7.2.3 Подпрограмма пересчета [3]
• 7.2.4 Подпрограмма выхода [5]
• Заключение
• Литература
• Приложения

Банк — это институт, который имеет право на основании лицензии НБРБ осуществлять различную финансовую деятельность.

Любая экономика держится на объективных экономических законах, одним из которых является закон денежного обращения. Оборот денег происходит под влиянием финансовых институтов, и прежде всего банков, которые создают основу для денежного оборота и связаны со всеми отраслями и секторами экономики. Банки обеспечивают финансирование всех сфер предпринимательства, производственной и непроизводственной сфер, сферы управления. Главная сущность банка—организация денежно-кредитного процесса.

Сегодня коммерческие банки способны предоставить клиентам до 200 видов банковских продуктов и операций. Такая обширная деятельность банков требует системного подхода к изучению банковского дела.

1. Структура коммерческого банка

Организационное устройство коммерческих банков соответствует общепринятой схеме управления акционерным обществом (рис 1.1). Высшим органом акционерного коммерческого банка является общее собрание акционеров, которое должно проходить не реже одного раза в год. На нем присутствуют акционеры или представители акционеров банка на основании доверенностей. Общее собрание правомочно решать вынесенные на его рассмотрение вопросы, если участники обеспечивают необходимый кворум. Вот вопросы, отнесенные к исключительной компетенции собрания, они не могут быть переданы на решение совету банка:

· внесение изменений и дополнений в Устав общества;

· реорганизация;

· определение количественного состава директоров;

· увеличение уставного капитала;

· образование исполнительного органа;

· утверждение аудитора и др.

Рис.1.1 Схема управления акционерным банком

Общее руководство деятельностью банка осуществляет совет банка. Функции совета банка:

· определяет приоритетные направления деятельности банка;

· рассматривает проекты кредитных и других планов банка;

· утверждает планы доходов, расходов и прибыли банка;

· рассматривает вопросы об открытии и закрытии филиалов банка;

· формирует состав правления банка;

· контролирует работу правления банка и др.

Непосредственное руководство текущей деятельностью коммерческого банка осуществляет правление. Оно несет ответственность перед общим собранием акционеров и Советом банка. Правление состоит из председателя правления, его заместителей и других членов. Заседания правления банка проводятся регулярно. Решения принимаются большинством голосов. При равенстве голосов голос председателя является решающим. Решения правления оформляются в форме приказа Председателя правления банка.

При правлении банка создается кредитный комитет. В функции кредитного комитета входят:

· разработка кредитной политики банка, структуры привлекаемых средств и их размещения;

· подготовка заключений по предоставлению ссуд;

· рассмотрение вопросов, связанных с инвестированием и ведением трастовых операций.

Ревизионная комиссия избирается Общим собранием акционеров. Порядок деятельности комиссии определяется внутренними документами общества, утверждается общим собранием собственников банка. В состав ревизионной комиссии не могут быть избраны члены совета банка и правления банка. Правление банка предоставляет в распоряжение ревизионной комиссии все необходимые для проведения ревизии материалы. Ревизионная комиссия подотчетна общему собранию акционеров.

Для обеспечения гласности в работе коммерческих банков и доступности информации об их финансовом положении годовые балансы банков, утвержденные общим собранием акционеров, а также отчет о прибылях и убытках должны (после подтверждения достоверности предоставленных в них сведений аудиторской организацией) публиковаться в печати.

В целях оперативного кредитно-расчетного обслуживания предприятий и организаций — клиентов банка, территориально удаленных от места расположения коммерческого банка, он может организовывать филиалы и представительства.

Филиалами банка считаются обособленные структурные подразделения, расположенные вне места его нахождения и осуществляющие все или часть его функций. Филиал не является юридическим лицом и совершает делегированные ему головным банком операции в пределах, предусмотренных лицензией Банка РБ. Он заключает договоры и ведет иную хозяйственную деятельность от имени коммерческого банка, его создавшего.

Представительство является обособленным подразделением коммерческого банка, которое расположено вне места нахождения банка, не обладает правами юридического лица и не имеет самостоятельного баланса. Оно создается для обеспечения представительских функций банка, совершения сделок и иных правовых действий. Представительство не занимается расчетно-кредитным обслуживанием клиентов. Для осуществления хозяйственных расчетов ему открывается текущий счет.

2. Основные операции коммерческого банка

К концу XX в. коммерческий банк способен предложить клиенту до 200 видов разнообразных банковских продуктов и услуг. Широкая диверсификация операций позволяет банкам сохранять клиентов и оставаться рентабельными даже при весьма неблагоприятной рыночной конъюнктуре.

Классификация операций коммерческого банка:

Пассивные операции

Активные операции

Комиссионные операции

2.1 Пассивные операции

Пассивные операции — это совокупность операций, обеспечивающих формирование ресурсов коммерческого банка. Ресурсы коммерческого банка могут быть сформированы за счет собственных и привлеченных средств. К собственным средствам коммерческого банка относятся:

1. уставной капитал (формируется за счет собственных средств частников и служит обеспечением его ликвидности);

2. резервный фонд (формируется за счет отчислений от прибыли и служит для покрытия убытков, возникающих при основной деятельности банка).

3. обязательные резервы для покрытия возможных потерь по ссудам и от операций с ценными бумагами;

4. нераспределенная прибыль (часть прибыли, остающаяся после платежей в бюджет, отчислений в резервный капитал, специальные фонды выплаты дивидендов).

Собственные ресурсы банка имеют огромное значение в поддержании его устойчивости и ликвидности.

Немаловажную роль играют и привлеченные средства. Мобилизуя временно свободные средства юридических и физических лиц на рынке финансовых ресурсов, коммерческие банки удовлетворяют потребности экономики в дополнительных оборотных и инвестиционных средствах. Привлеченные средства формируются посредством следующих банковских операций:

· привлечение кредитов и займов, полученных от других юридических лиц;

· депозитные операции;

Депозитные операции — это операции банков по привлечению денежных средств юридических и физических лиц во вклады на определенный срок либо до востребования. Объектами депозитных операций являются депозиты — суммы денежных средств, которые субъекты депозитных операций вносят в банк и которые на определенное время оседают на счетах в банке в силу действующего порядка осуществления банковских операций. По экономическому содержанию депозиты принято подразделять на 3 группы:

ь срочные депозиты;

ь депозиты до востребования;

ь сберегательные вклады населения.

Каждая из этих групп классифицируется по разным признакам. Срочные депозиты классифицируются в зависимости от их срока, например:

ы депозиты со сроком до 3 месяцев;

ы депозиты со сроком от 3 до 6 месяцев;

ы депозиты со сроком от 6 до 9 месяцев;

ы депозиты со сроком от 9 до 12 месяцев и т. д.

Депозиты до востребования классифицируются в зависимости от характера и принадлежности средств, хранящихся на счетах.

2.2 Активные операции

Активные операции — операции по размещению собственных и привлеченных средств банка для получения прибыли. От качественного осуществления активных операций банка зависят ликвидность, доходность, а, следовательно, финансовая надежность и устойчивость банка в целом. Активные операции банка в зависимости от их экономического содержания делятся на:

Ш кредитные (ссудные);

Ш инвестиционные;

Ш гарантийные;

Ш операции с ценными бумагами.

Основу активных операций составляют кредитные операции. Они являются наиболее рискованными и вследствие этого наиболее доходными. Банковские кредиты классифицируются по ряду признаков:

а) По сроку погашения:

· краткосрочные

· среднесрочные

· долгосрочные

б) По способу погашения:

· погашаемые одной суммой в конце срока

· погашаемые равными долями через равные промежутки времени

· погашаемые неравными долями через различные промежутки времени

· сложный кредит (с выплатой от 20 до 50% суммы кредита в конце срока)

· прогрессивный кредит (с прогрессивно нарастающими к концу срока действия кредитного договора выплатами)

· сезонный кредит (кредит для сезонных производств с выплатами только в те месяцы, на которые приходятся максимальные суммы выручки)

в) По способу взимания ссудного процента:

· плата в момент погашения ссуды

· плата равномерными взносами в течение всего срока действия кредитного договора

· оплата в момент выдачи кредита

г) По целевому назначению:

· связанные (целевые) ссуды

· на финансирование производственных затрат

· финансирование текущих производственных затрат

· финансирование инвестиционных затрат

· потребительские кредиты

· ссуды общего характера (нецелевые, несвязанные)

д) По форме предоставления кредита:

· ссуды в безналичной форме:

· зачисление безналичных денег на соответствующий счет заёмщика;

· в смешанной форме (сочетание 2-х предыдущих вариантов).

· ссуды в налично-денежной форме

Для классификации кредитов на те или иные группы и виды могут использоваться и другие критерии. Банковские операции базируются на методологической основе, одним из элементов которой являются принципы кредитования:

1. Принцип возвратности (в определённый договором срок вся сумма кредита должна быть возвращена полностью).

2. Принцип срочности (кредит даётся на определённый указанный срок, который должен быть обоснован и зависит от оборачиваемости капитала).

3. Принцип платности (за право пользования кредитом заёмщик должен заплатить оговоренную сумму процентов).

4. Принцип подчинения кредитной сделки нормам законодательства и банковским правилам.

5. Принцип неизменности условий кредитования (изменение условий кредитного договора должно производиться в соответствии с правилами, сформулированными в самом кредитном договоре либо в специальном приложении к нему).

При выдаче кредита банк требует от потенциального заемщика комплект документов, характеризующих материальную обеспеченность кредита и юридическую правомочность заемщика.

Инвестиционные операции банка — операции по инвестированию банком своих средств в ценные бумаги и паи небанковских структур в целях совместной хозяйственно-финансовой и коммерческой деятельности, а также размещения в виде срочных вкладов в других кредитных организациях.

Гарантийные операции — операции по выдаче банком гарантии (поручительства) уплаты долга клиента третьему лицу при наступлении определенных условий.

2.3 Комиссионные операции

Комиссионные операции — это такие операции, которые банк выполняет по поручению своих клиентов и взимает с них плату в виде комиссионных. Число этих операций постоянно растет, а на их осуществление банком не отвлекаются собственные или привлеченные средства. К числу основных комиссионных операций относятся:

1. Расчетно-кассовые операции — связаны с ведением счетов в рублях и иностранной валюте, осуществлением расчетов и платежей клиента, а также получением и зачислением перечисленных ему средств на счет в безналичной форме, с выдачей наличных денег со счета, внесением их на счет, хранением и перевозкой. Они оформляются соответствующим договором о кассовом обслуживании.

2. Трастовые операции, которые представлены следующими услугами:

a) передача в доверительное управление отдельных ценных бумаг и их портфелей;

b) платежные функции, связанные с обслуживанием ценных бумаг;

c) управление активами пенсионных и инвестиционных фондов и др.

3. Операции с иностранной валютой, которые помимо традиционных операций купли-продажи иностранной валюты включают оплату и выставление денежных аккредитивов, покупку и оплату дорожных чеков иностранных банков, выпуск и обслуживании пластиковых карт, осуществление международных расчетов.

4. Информационные услуги, когда банки за определенную плату при доставляют клиентам информацию как коммерческого, так и не коммерческого характера.

3. Язык программирования Visual Basic for Applications

Visual Basic for Applications (VBA) — немного упрощённая реализация языка программирования Visual Basic, встроенная в линейку продуктов Microsoft Office (включая версии для Mac OS), а также во многие другие программные пакеты, такие как AutoCAD, WordPerfect и ESRI ArcGIS. VBA покрывает и расширяет функциональность ранее использовавшихся специализированных макроязыков, таких как WordBasic. Может использоваться для управления приложением (меню, панели инструментов, формы пользователя, диалоговые окна и т. д.). VBA может также использоваться для создания импорта и экспорта различных форматов файлов.

VBA является интерпретируемым языком. Как и следует из его названия, VBA близок к Visual Basic, но может выполняться лишь в рамках приложения, в которое он встроен. Кроме того, он может использоваться для управления одним приложением из другого, с помощью OLE Automation (например, таким образом можно создать документ Word на основе данных из Excel).

VBA функционально богат и чрезвычайно гибок, но имеет ряд ограничений, включая ограниченную поддержку указателей на функции (используются в Windows API). Имеется возможность использовать (но не создавать) библиотеки DLL.

VBA — транслируемый язык. Это означает, что его команды сначала преобразуются к исполняемой форме. Контейнеры VBA (Access, Excel, Word) могут сохранять оттранслированную программу в скрытой форме в исходном документе (чтобы не требовалась перекомпиляция). Исполнение кода происходит в виртуальной машине (как в языке Java), что зачастую ошибочно называют интерпретацией. Несмотря на его схожесть со многими старыми диалектами BASIC (особенно MS BASIC), VBA не совместим с ними кроме Microsoft Visual Basic до версии 6 и не совместим с VB.net.

Чтобы использовать VBA с приложениями (Access, Excel, Word) необходимо использовать соответствующую терминологию и языковые конструкции. Это так называемая «объектная модель приложения». Карта объектной модели является сетевой для Excel, Word. Новичку использование объектной модели может составлять значительную трудность.

Широкое распространение Visual Basic for Applications в сочетании с изначально недостаточным вниманием к вопросам безопасности привело к широкому распространению макровирусов.

Как любой язык программирования макросы VBA могут быть созданы с злым умыслом. Для защиты были введены опции приложения. Пользователь по своему усмотрению может либо исполнять макроопределения VBA или не исполнять.

В будущем Microsoft планирует заменить VBA на Visual Studio Tools for Applications (VSTA) — инструментарий расширения функциональности приложений, основанный на Microsoft.net.

4. Постановка задачи

Необходимо разработать программу для автоматического расчета кредитных и депозитных операций при помощи Visual Basic for Applications. Программа должна:

1. запрашивать у пользователя все входные данные с помощью формы или диалоговых окон;

2. автоматически рассчитывать все выходные данные кредитных и депозитных операций;

3. автоматически строить графики на основе рассчитанных значений на другом листе книги;

4. автоматически предусматривать возможность выбора пользователем одного из видов платежа (аннуитетный или дифференцированный) для кредитных операций;

5. автоматически предусматривать возможность выбора пользователем вида начисления процентов (простой и сложный) для депозитных операций

Пользователь должен иметь возможность сохранить рабочую книгу под удобным для него именем. При разработке программы учесть параметры Варианта № 20:

5. Математическая формулировка задачи

Белорусские банки применяют в настоящий момент два способа погашения долга — аннуитетными (равными) и дифференцированными (уменьшающимися) платежами. Аннуитетные платежи иногда еще называют рентными, а способ погашения кредита дифференцированными платежами — коммерческим. Могут встречаться и другие определения.

5.1 Расчёт ежемесячных выплат при аннуитетных платежах

Ежемесячный аннуитетный платеж — это постоянная сумма, которую заемщик каждый месяц отдает банку. Он складывается из двух составляющих — возвращения основного долга и начисленных процентов. (Строго говоря, понятие «аннуитет» применимо только к ежегодным выплатам, но на практике сложилось так, что его применяют к равным выплатам с любой периодичностью, в том числе и ежемесячным.) Формула аннуитетного платежа выглядит следующим образом:

ЕП=СК*ПС/12 (1/ (1- (1+ПС/12) ^СК)), где

ЕП — размер ежемесячного платежа; СК — сумма кредита; ПС — годовая процентная ставка; КМ — количество месяцев (срок, на который выдан кредит).

Как мы уже заметили, ежемесячный аннуитетный платеж складывается из двух составляющих — возвращения основного долга и начисленных процентов:

ЕП=ВОД+ЕПВ, где

ВОД — возврат основного долга; ЕПВ — ежемесячные процентные выплаты.

На этом сходство в подходах банков заканчивается, и начинаются различия. Состоят они в подходах к вычислению суммы причитающихся процентов. Основных подходов два, разница — в используемой временной базе. Часть банков исходят из того, что «в году 12 месяцев», и тогда размер ежемесячных процентных выплат определяется по формуле:

ЕПВ=ОЗ*ПС/12, где

ЕПВ — ежемесячные процентные выплаты; ОЗ — остаток задолженности в данном месяце; ПС — годовая процентная ставка.

Часть банков исходит из того, что «в году 365 дней» и такой подход называется расчетом точных процентов с точным числом дней ссуды. Для того чтобы вычислить сумму возврата основного долга, необходимо из суммы ежемесячного аннуитетного платежа (размер которого, как мы помним, остается неизменным) вычесть размер процентных выплат в данном месяце:

ВОД=ЕП-ЕПВ

5.2 Расчёт ежемесячных выплат при дифференцированных платежах

Дифференцированный платеж складывается из суммы двух составляющих — суммы начисленных процентов и суммы платежа по основному долга (телу кредита). Дифференцированный платеж можно представить в виде формулы:

ПК=ОД+ПП,

где

ПК — платеж по кредиту, ОД — сумма гашения основного долга, ПП — сумма процентов за расчетный период.

Сумма платежа по основному долгу равна отношению суммы кредита к количеству месяцев, на который выдан кредит. Формула расчета суммы платежа по основному долгу имеет следующий вид:

ОД=ПСК/КМ, где

ПСК — полная сумма кредита, КМ — количество месяцев, на которое выдан кредит.

Сумма процентов рассчитывается на остаток ссудной задолженности.

ПП = ОСЗ*КД*ГПС/365, где

КД — количество дней, за которые выполняется начисление, ГПС — годовая процентная ставка (в долях), ОСЗ — остаток ссудной задолженности.

С каждым платежом сумма процентов будет уменьшаться, а сумма платежа по основному долгу останется неизменной. Максимальная сумма платежа придется на первое гашение по кредиту.

алгоритм программа операция банк

6. Описание работы программы

Основной программа состоит из 4-х форм и файла Microsoft Excel, который создается непосредственно в ходе работы программы.

При запуске рабочей книги открывается первая форма (рис 6.1) на которой вы наблюдаете 3 кнопки. При нажатии кнопки «Выход» программа прекращает свою работу и закрывает открытые книги. При нажатии кнопки «Кредитные операции» создается новая рабочая книга и открывается форма 2, где вы вводите информацию необходимую для расчета кредитных операций, а при нажатии кнопки «Депозитные операции» также создается новая рабочая книга, но открывается форма 3, где вводится информация необходимая для расчета депозитных операций.

Рис 6.1 Первая форма

На Форме 2 (рис 6.2) мы увидим поля, в которые нужно ввести ФИО клиента, сумму кредита, срок на который берется кредит, и выбрать вид платежа: аннуитетный или дифференцированный. Если какие либо данные не введены на экране появляется сообщение о том, что что-то не введено. После ввода всех данных вы нажимаете кнопку «Расчет» и программа по введенным параметрам рассчитывает выходные данные, которые для удобства размещены в виде таблицы на первом листе созданной рабочей книги, а на втором листе размещает диаграмму, на которой вы увидите 3 зависимости:

Ш Плата по основному долгу от времени

Ш Зависимость величины платы по % со временем

Ш Как изменяется сумма ежемесячного платежа

Рис 6.2 Форма2 для расчета кредитных операций

Для детального ознакомления с полученными данными вам необходимо будет сохранить рабочую книгу, что возможно сделать, нажав кнопку «Сохранить» в окне которое появится (Рис 6.5). Данная книга сохранится под именем клиента, путь сохранения выбираете вы сами.

На форме 2 также находятся еще одна кнопка «Выход» , при нажатии которой появляется диалоговое окно, в котором у вас спрашивают «Вы действительно хотите выйти?» :

Рис 6.3

При нажатии кнопки «Да» программа закрывает все открытые книги Excel.

Рис 6.4 Форма3 для расчета депозитных операций

На рис 6.4 мы увидим окно, где вводятся данные для расчета депозитных операций: ФИО клиента, начальная сумма депозита, срок на который ложится депозит, и выбирается вид процента (сложный процент или простой процент). Если какие либо данные не введены на экране появляется сообщение о том, что что-то не введено. После ввода всех данных вы нажимаете кнопку «Расчет» и программа по введенным параметрам рассчитывает выходные данные, которые также размещены в виде таблицы на первом листе. На втором листе размещается диаграмма, на которой изображены 3 зависимости:

Ш Основная сумма депозита от времени

Ш Зависимость величины % от количества времени

Ш Как изменяется общая сумма

Для детального ознакомления с полученными данными вам также необходимо будет сохранить рабочую книгу, что возможно сделать, нажав кнопку «Сохранить» в окне которое появится (Рис 6.5). Данная книга сохранится под именем клиента, путь сохранения выбираете вы сами.

На форме 3 также находятся еще одна кнопка «Выход» , при нажатии которой появляется диалоговое окно, изображенное на рис 6.3.

Рис 6.5 Форма 4

На рис 4 изображена четвертая форма с тремя копками:

· " Сохранить"

· " Выйти" - при нажатии которой появляется диалоговое окно изображенное на рис 6.3 и затем программа спрашивает о возможном сохранении (рис 6.6).

Рис 6.6

· " Пересчитать" - при нажатии которой также появляется диалоговое окно о возможном сохранении (рис 7), так как если данные были не сохранены они будут утеряны и после вашего выбора программа вновь перейдет на форму 1 (рис 2)

7. Разработка модуля программы расчёта кредитов и депозитов

7.1 Схема алгоритма выполнения основной программы

Рис 7.1 Схема алгоритма выполнения программы

Блок1 Обозначает выбор кредитных или депозитных операций в окне представленном на рисунке 6.1.

Блок 2, 3 Создают новую рабочую книгу

Блок 4 Подпрограмма расчета депозита выводит окно для ввода необходимых для расчетов данных, проверяет все ли они введены и удовлетворяют ли условиям, и по введенным параметрам рассчитывает выходные данные, затем строит по ним графики. Окно для ввода данных представлено на рисунке 6.4 Листинг подпрограммы можно просмотреть в приложении 2 под названием rasdep, схема алгоритма подпрограммы изображена на рисунке 7.4

Блок 5 Подпрограмма расчета кредита выводит окно для ввода необходимых для расчетов данных, проверяет все ли они введены и удовлетворяют ли условиям, и по введенным параметрам рассчитывает выходные данные, затем строит по ним графики. Окно для ввода данных представлено на рисунке 6.2 Листинг подпрограммы можно просмотреть в приложении 2 под названием raskred, схема алгоритма подпрограммы изображена на рисунке 7.3

Блок 6 Обозначает нажатие кнопки пересчитать на форме изображенной на рисунке 6.5

Блок 7 При запуске подпрограммы пересчета данных появляется диалоговое окно о возможном сохранении (рис 6.6), так как если данные были не сохранены они будут утеряны при закрытии созданной книги. Листинг подпрограммы можно просмотреть в приложении 2 под названием peres, схема алгоритма подпрограммы изображена на рисунке 7.5

Блок 8 Подпрограмма сохранения активируется при нажатии кнопки «Сохранить» в окне изображенном на рисунке 6.5 Листинг подпрограммы можно просмотреть в приложении 2 под названием sohr, схема алгоритма подпрограммы изображена на рисунке 7.2

Блок 9 Подпрограмма выхода, что запускается при нажатии кнопки «Выход» расположенной в окне изображенном на рисунке 6.5 В результате программа выводит два диалоговых окна изображенных на рисунках 6.3 и 6.6 одно из них о возможном сохранении рабочей книги и затем работа программы прекращается. Листинг подпрограммы можно просмотреть в приложении 2 под названием vih, схема алгоритма подпрограммы изображена на рисунке 7.6

7.2 Используемые подпрограммы

7.2.1 Подпрограмма сохранения [4]

Рис 7.2 Схема алгоритма подпрограммы сохранения файла

Блок 1 проверяет введено ли ФИО клиента в третьей форме,

Блок 2 присваивание промежуточной переменной i ФИО клиента в 3-й форме.

Блок 3 присваивание промежуточной переменной i ФИО клиента во 2-й форме.

Блок 4 присваивание рабочей книги имени i

Блок 5 проверяет условие нажата ли кнопка отмены, если да то завершение подпрограммы.

Блок 6 сохранение рабочей книги.

7.2.2 Подпрограмма расчета кредита [2]

Рис 7.3 Схема алгоритма подпрограммы расчета кредита

Подпрограмма расчета кредита выводит окно для ввода необходимых для расчетов данных, проверяет все ли они введены и удовлетворяют ли условиям, и по введенным параметрам рассчитывает выходные данные, затем строит по ним графики.

Подпрограмма расчета депозита [1]

Рис 7.4 Схема алгоритма подпрограммы расчета депозита

Подпрограмма расчета депозита выводит окно для ввода необходимых для расчетов данных, проверяет все ли они введены и удовлетворяют ли условиям, и по введенным параметрам рассчитывает выходные данные, затем строит по ним графики. Окно для ввода данных представлено на рисунке 6.4.

7.2.3 Подпрограмма пересчета [3]

Рис 7.5 Схема алгоритма подпрограммы пересчета

При запуске подпрограммы пересчета данных появляется диалоговое окно о возможном сохранении (рис 6.6), так как если данные были не сохранены они будут утеряны при закрытии созданной книги. После подпрограмма закрывает UserForm4 (рис 6.5) и открывает UserForm1 (рис 6.1).

7.2.4 Подпрограмма выхода [5]

Рис 7.6 Схема алгоритма подпрограммы выхода

Подпрограмма выхода, что запускается при нажатии кнопки «Выход» расположенной в окне изображенном на рисунке 6.5 В результате программа выводит два диалоговых окна изображенных на рисунках 6.3 и 6.6 одно из них о возможном сохранении рабочей книги и затем работа программы прекращается.

Заключение

В данной курсовой я ознакомилась с использованием Basic for Applications в автоматизации банковских операций. Также ознакомилась со структурой банка и видами банковских операций. Создала программу для расчёта кредитных и депозитных операций при помощи Visual Basic for Applications. В программе реализованы все поставленные задачи:

программа запрашивает у пользователя все входные данные с помощью формы;

автоматически рассчитываются все выходные данные кредитных и депозитных операций;

автоматически строятся графики на основе рассчитанных значений на другом листе книги;

предусмотрена возможность выбора пользователем одного из видов платежа (аннуитетный или дифференцированный) для кредитных операций;

предусмотрена возможность выбора пользователем вида начисления процентов (простой и сложный) для депозитных операций

при разработке программы учтены параметры Варианта № 20:

1. Андрей Гарнаев." Использование MS Excel и VBA в экономике и финансах" .

2. Джон Уокенбах. «Программирование на VBA в Excel 2002». Киев. 2003 г.

3.А. Н. Пылкин, Л. А. Демидова «Программирование в среде Visual for Applications» .

4. Конспект лекций

Приложения

Приложение 1. «Примеры расчетов»

1. Пример расчета кредита дифференцированным методом В форме 1 изображенной на рисунке 6.1 выбираем кредитные операции, затем вводим необходимые данные:

сумма кредита-5 000 000

срок кредита-15лет

Рис 1. UserForm2

Результаты:

Рис 2. Лист1 с результатами расчета График:

Рис 3. График платежей кредита

2. Пример расчета депозита со сложным процентом В форме 1 изображенной на рисунке 6.1 выбираем депозитные операции, затем вводим необходимые данные:

сумма депозита-5 000 000

срок депозита-15мес

Рис 4. UserForm3

Результаты:

Рис 5. Лист1 с результатами расчета График:

Рис 6. График платежей депозита

Приложение 2. «Листинг программы»

'переход к первой форме сразу при открытии рабочей книги

Private Sub Workbook_Open ()

'переход к первой форме

UserForm1. Show

End Sub

UserForm1

'Переход к форме расчета кредита

Private Sub CommandButton1_Click ()

'создание новой книги

Workbooks. Add

UserForm1. Hide

UserForm2. Show

End Sub

'Переход к форме расчета депозита

Private Sub CommandButton2_Click ()

'создание новой книги

Workbooks. Add

UserForm1. Hide

UserForm3. Show

End Sub

' процедура выхода

Private Sub vix_Click ()

' запрос у пользователя на подтверждение выхода

Ex = MsgBox («Вы действительно хотите выйти?», vbYesNo + vbQuestion, Zagl)

If Ex = vbNo Then Exit Sub

' закрытие рабочих книг

ActiveWorkbook. Close False

ThisWorkbook. Close

End Sub

UserForm2

' подпрограмма рассчета кредитов

Private Sub rascred_Click () [2]

Dim Pz, Summ, Sroc As Single

Dim ost, osn, proc, plat, Sum1, Sum2, Sum3 As Single

Dim i As Integer

Const Zaglav As String = «Кредитные и депозитные операции»

' показать ошибку, если не введена ФИО клиента

If fio. Value = «» Then

MsgBox «Введите ФИО клиента», Zaglav

Exit Sub

End If

' показать ошибку, если не выбран вид платежа

If an. Value = False And dif. Value = False Then

MsgBox «выберите вид платежа», Zaglav

Exit Sub

End If

' ошибка, если не введена сумма кредита

If summkred. Value = «» Then

MsgBox «Введите сумму кредита», Zaglav

Exit Sub

End If

' ошибка, если не введена сумма кредита

If srockred. Value = «» Then

MsgBox «Введите срок кредитования», Zaglav

Exit Sub

End If

' ошибка, если неправильно введена сумма кредита

If IsNumeric (summkred. Value) = False Then

MsgBox «Неправильно введена сумма кредита», Zaglav

Exit Sub

End If

' ошибка, если неправильно введен срок кредитования

If IsNumeric (srockred. Value) = False Then

MsgBox «Неправильно введен срок кредитования», Zaglav

Exit Sub

End If

' присвоение переменным значений, вводимых пользователем

Summ = CDbl (summkred. Value)

Sroc = CInt (srockred. Value)

' проверка срока кредитования

If Sroc < 1 Then

MsgBox «Минимальный срок кредитования — 1 год», Zaglav

Exit Sub

End If

If Sroc > 40 Then

MsgBox «Максимальный срок кредитования — 40 лет», Zaglav

Exit Sub

End If

' приcваивание процентной ставки в зависимости от срока кредитования

If Sroc >= 1 And Sroc <= 3 Then Pz = 0.1

If Sroc > 3 And Sroc <= 5 Then Pz = 0.09

If Sroc > 5 Then Pz = 0.08

' очистка ячеек

Cells. Clear

' ввод значений

Range («A1»). Value = «Ф. И.О. клиента: «

Range («A2»). Value = «Тип операции: «

Range («A3»). Value = «Тип платежа: «

Range («A4»). Value = «Сумма кредита: «

Range («A5»). Value = «Срок кредитования: «

Range («A6»). Value = «Процентная ставка: «

Range («A7»). Value = «Первоначальный взнос (%): «

Range («A8»). Value = «Первоначальный взнос: «

Range («A10: D10»). Font. Bold = True

Range («A10»). Value = «№ месяца»

Range («B10»). Value = «Оплата по основному долгу»

Range («C10»). Value = «Оплата по процентам»

Range («D10»). Value = «Ежемесячные платежи»

Range («E10»). Value = «Итого: «

Range («E10»). Font. Bold = True

Range («F10»). Value = «Плата по основному долгу»

Range («G10»). Value = «Плата по процентам»

Range («H10»). Value = «Общая плата: «

' определение формата ячеек

Range («B1»). NumberFormat = «general»

Range («B2»). NumberFormat = «Кредит»

Range («B3»). NumberFormat = «general» 'общий

Range («B4»). NumberFormat = «0.00 $» 'денежный

Range («B5»). NumberFormat = «0год (а)»

Range («B6»). NumberFormat = «0.00%»

Range («B7»). NumberFormat = «0.00%»

Range («B8»). NumberFormat = «0.00 $»

Range («B11: D500»). NumberFormat = «0.00 $»

Range («E11: H11»). NumberFormat = «0.00 $»

' определение ширины колонок

Columns («A: H»). ColumnWidth = 20

' ввод исходных значений в рабочий лист

Range («B1»). Value = fio. Value

Range («B2»). Value = «Кредит»

Range («B4»). Value = Summ

Range («B5»). Value = Sroc

Range («B6»). Value = Pz

Range («B7»). Value = 0.5

Range («B8»). Value = Summ * 0.5

Summ = Summ — Summ * 0.5

' обнуление переменных

ost = 0

osn = 0

proc = 0

plat = 0

Sum1 = 0

Sum2 = 0

Sum3 = 0

' рассчет кредита дифференцированным методом

If dif. Value = True Then

Range («B3»). Value = «Дифференцированный»

osn = Summ / (12 * Sroc)

' расчет выплаты по основному долгу, выплаты по процентам, и всего долга

For i = 1 To Sroc * 12

ost = Summ — (i — 1) * osn

proc = ost * Pz / 12

plat = proc + osn

Sum1 = Sum1 + osn

Sum2 = Sum2 + proc

Sum3 = Sum3 + plat

'занесение данных в таблицу

Cells (i + 10, 1). Value = i

Cells (i + 10,2). Value = osn

Cells (i + 10,3). Value = proc

Cells (i + 10,4). Value = plat

Next i

' вывод итогов

Range («F11»). Value = Sum1

Range («G11»). Value = Sum2

Range («H11»). Value = Sum3

End If

' рассчет кредитов аннуительным методом

If an. Value = True Then

Range («B3»). Value = «Аннуитетный»

plat = (Summ * (Pz / 12)) / (1 — (1 + Pz / 12) ^ (-Sroc * 12)) ' сумма ежемесечного платежа

' расчет выплаты по основному долгу, выплаты по процентам, и всего долга

For i = 1 To Sroc * 12

ost = Summ — Sum1

proc = ost * Pz / 12

osn = plat — proc

Sum1 = Sum1 + osn

Sum2 = Sum2 + proc

Sum3 = Sum3 + plat

'занесение данных в таблицу

Cells (i + 10, 1). Value = i

Cells (i + 10,2). Value = osn

Cells (i + 10,3). Value = proc

Cells (i + 10,4). Value = plat

Next i

' вывод итогов

Range («F11»). Value = Sum1

Range («G11»). Value = Sum2

Range («H11»). Value = Sum3

End If

'построение графика

Sheets. Add After: =Sheets (Sheets. Count)

ActiveSheet. Shapes. AddChart. Select

ActiveChart. ChartType = xlLineMarkers

ActiveChart. SetSourceData Source: =Range (Worksheets (1). Cells (10,2), Worksheets (1). Cells (Sroc * 12 + 10,4))

ActiveChart. SeriesCollection (1). Name = «=Лист1! $B$ 10»

ActiveChart. SeriesCollection (2). Name = «=Лист1! $C$ 10»

ActiveChart. SeriesCollection (3). Name = «=Лист1! $D$ 10»

ActiveChart. Location Where: =xlLocationAsNewSheet

'закрывает форму 2 и открывает форму 4

UserForm2. Hide

UserForm4. Show

End Sub

'процедура выхода

Private Sub vix_Click ()

' запрос у пользователя на подтверждение выхода

Ex = MsgBox («Вы действительно хотите выйти?», vbYesNo + vbQuestion, Zaglav)

If Ex = vbNo Then Exit Sub

' закрытие рабочих книг

ActiveWorkbook. Close False

ThisWorkbook. Close

End Sub

UserForm3

' программа расчета депозита

Private Sub rasdep_Click () [1]

Dim Pz, Summ, Sroc As Single

Dim osn, proc, plat, Sum1, Sum2 As Single

Dim i As Integer

Const Zaglav As String = «Кредитные и депозитные операции»

' показать ошибку, если не введена ФИО клиента

If fio. Value = «» Then

MsgBox «Введите ФИО клиента», Zaglav

Exit Sub

End If

' показать ошибку, если не выбран вид депозита

If pr. Value = False And sl. Value = False Then

MsgBox «выберите вид депозита», Zaglav

Exit Sub

End If

' показать ошибку, если не введена сумма депозита

If summdep. Value = «» Then

MsgBox «Введите сумму депозита», Zaglav

Exit Sub

End If

' показать ошибку, если не введена сумма депозита

If srocdep. Value = «» Then

MsgBox «Введите срок депозита», Zaglav

Exit Sub

End If

' показать ошибку, если неправильно введена сумма депозита

If IsNumeric (summdep. Value) = False Then

MsgBox «Неправильно введена сумма депозита», Zaglav

Exit Sub

End If

' показать ошибку, если неправильно введен срок депозита

If IsNumeric (srocdep. Value) = False Then

MsgBox «Неправильно введен срок депозита», Zaglav

Exit Sub

End If

' присвоение переменным значений, вводимых пользователем

Summ = CDbl (summdep. Value)

Sroc = CInt (srocdep. Value)

' проверка на правильность ввода срока депозита

If Sroc < 1 Then

MsgBox «Минимальный срок депозита — 1 месяц», Zaglav

Exit Sub

End If

If Sroc > 120 Then

MsgBox «Максимальный срок депозита — 10 лет», Zaglav

Exit Sub

End If

' приcваивание процентной ставки в зависимости от срока депозита

If Sroc >= 1 And Sroc <= 24 Then Pz = 0.04

If Sroc > 24 And Sroc <= 60 Then Pz = 0.08

If Sroc > 60 Then Pz = 0.09

' проверка вводимой суммы депозита

If Summ < 650 Then

MsgBox «Минимальная сумма депозита — 650 рублей», Zaglav

Exit Sub

End If

' очистка ячеек

Cells. Clear

Range («A1»). Value = «Ф. И.О. клиента: «

Range («A2»). Value = «Тип операции: «

Range («A3»). Value = «Метод расчета: «

Range («A4»). Value = «Сумма депозита, руб: «

Range («A5»). Value = «Срок депозита, мес: «

Range («A6»). Value = «Процентная ставка: «

Range («A10»). Value = «№ месяца»

Range («B10»). Value = «Основной депозит, руб»

Range («C10»). Value = «Проценты, руб»

Range («D10»). Value = «Сумма, руб»

Range («E11»). Value = «Итого: «

Range («F10»). Value = «Депозит»

Range («G10»). Value = «Проценты»

Range («H10»). Value = «Всего»

' определение формата ячеек

Range («B1»). NumberFormat = «general»

Range («B2»). NumberFormat = «general»

Range («B3»). NumberFormat = «general»

Range («B4»). NumberFormat = «0.00 $»

Range («B5»). NumberFormat = «0мес»

Range («B6»). NumberFormat = «0.00%»

Range («B7»). NumberFormat = «0.00 $»

Range («A10: D10»). Font. Bold = True

Range («B9: D140»). NumberFormat = «0.00 $»

Range («F11: H11»). NumberFormat = «0.00 $»

' определение ширины колонок

Columns («A: H»). ColumnWidth = 25

' ввод исходных значений в рабочий лист

Range («B1»). Value = fio. Value

Range («B2»). Value = «Депозит»

Range («B4»). Value = Summ

Range («B5»). Value = Sroc

Range («B6»). Value = Pz

' обнуление переменных

osn = 0

proc = 0

plat = 0

Sum1 = 0

Sum2 = 0

' рассчет депозита c простым процентом

If pr. Value = True Then

Range («B3»). Value = «Простой процент»

proc = Summ * Pz / 12

osn = Summ

For i = 1 To Sroc

Sum1 = proc * i

plat = osn + Sum1

'Занесение данных в таблицу

Cells (i + 10, 1). Value = i

Cells (i + 10,2). Value = osn

Cells (i + 10,3). Value = Sum1

Cells (i + 10,4). Value = plat

Next i

' вывод итогов

Range («F11»). Value = osn

Range («G11»). Value = Sum1

Range («H11»). Value = plat

End If

'расчет депозита с сложным процентом

If sl. Value = True Then

Range («B3»). Value = «Сложный процент»

osn = Summ

Sum2 = Summ

For i = 1 To Sroc

proc = Sum2 * Pz / 12

plat = Sum2 + proc

Sum2 = plat

Sum1 = plat — osn

'Занесение данных в таблицу

Cells (i + 10, 1). Value = i

Cells (i + 10,2). Value = osn

Cells (i + 10,3). Value = Sum1

Cells (i + 10,4). Value = plat

Next i

' вывод итогов

Range («F11»). Value = osn

Range («G11»). Value = Sum1

Range («H11»). Value = plat

End If

'построение графика

Sheets. Add After: =Sheets (Sheets. Count)

ActiveSheet. Shapes. AddChart. Select

ActiveChart. ChartType = xlLineMarkers

ActiveChart. SetSourceData Source: =Range (Worksheets (1). Cells (10,2), Worksheets (1). Cells (Sroc + 10,4))

ActiveChart. SeriesCollection (1). Name = «=Лист1! $B$ 10»

ActiveChart. SeriesCollection (2). Name = «=Лист1! $C$ 10»

ActiveChart. SeriesCollection (3). Name = «=Лист1! $D$ 10»

ActiveChart. Location Where: =xlLocationAsNewSheet

'закрывает форму 2 и открывает форму 4

UserForm3. Hide

UserForm4. Show

End Sub

'процедура выхода

Private Sub vix_Click ()

' запрос у пользователя на подтверждение выхода

Ex = MsgBox («Вы действительно хотите выйти?», vbYesNo + vbQuestion, Zaglav)

If Ex = vbNo Then Exit Sub

' закрытие рабочих книг

ActiveWorkbook. Close False

ThisWorkbook. Close

End Sub

UserForm4

'Процедура сохранения расчетов

Private Sub sohr_Click () [4]

If UserForm3. fio. Value = «» Then

i = UserForm2. fio. Value

SaveName = Application. GetSaveAsFilename (InitialFileName: =i)

' при нажатии кнопки " отмена" выходит из процедуры

If SaveName = False Then Exit Sub

ActiveWorkbook. SaveAs Filename: =SaveName & «xls»

Exit Sub

End If

i = UserForm3. fio. Value

SaveName = Application. GetSaveAsFilename (InitialFileName: =i)

' при нажатии кнопки " отмена" выходит из процедуры

If SaveName = False Then Exit Sub

ActiveWorkbook. SaveAs Filename: =SaveName & «xls»

End Sub

'процедура выхода

Private Sub vix_Click () [5]

' запрос у пользователя на подтверждение выхода

Ex = MsgBox («Вы действительно хотите выйти?», vbYesNo + vbQuestion, Zaglav)

If Ex = vbNo Then Exit Sub

' запрос на подтверждение сохранения

Sv = MsgBox («Сохранить текущий файл?», vbYesNo + vbQuestion, Zaglav)

If Sv = vbNo Then

ActiveWorkbook. Close False

ThisWorkbook. Close

Exit Sub

End If

' сохранение файла

If UserForm3. fio. Value = «» Then

i = UserForm2. fio. Value

SaveName = Application. GetSaveAsFilename (InitialFileName: =i)